parse an excel column to limit number of characters?

Question

I have an existing excel sheet, one column has product names, I need to parse each field in the column down to a max of 50 characters, currently they have up to 100 in each field.

Appreciate any help.

Answer 1

The first answer won't help you deal with the data on an existing sheet.



By "parse...down to a max. of 50 characters," I assume you mean that if a cell has more than 50 characters, you want to take just the first 50 characters.



Following is a formula which will return just the first 50 characters. Assume your product names are in col. A, starting with cell A1. In cell B1 enter the following formula:



=LEFT(A1,50)



This formula simply says that B1 equals the leftmost, i.e. first, 50 characters of whatever is in cell A1.



Copy and paste B1 from B2 down to the last row where you have product names in col. A. Now col. B will have the truncated names. If you want, you could select col. B, copy it and do a Paste Special - Values back to col. A, then just delete col. B.

Answer 2

If it is just a simple matter of truncating the text, then left(a1,50) would be sufficient. You may want a macro to strip all the vowels off but then the results may not be want you really want.

One option is to filter out the unique values using advance filter options. Copy this list of unique values to a new sheet, put them in column A. In column B, enter the shortened text manually using the appropriate acronymns or short words.

Go back to the original sheet, do a vlookup, you now have a shorten text in a new column.



Keywords to search: advanced filter, unique, vlookup

Answer 3

yes...and you could have found it too Restrict data entry to text of a specified length Select one or more cells to validate. On the Data tab, in the Data Tools group, click Data Validation. Issue: The Data Validation command is unavailable. An Excel table may be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range. You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC. The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section. In the Data Validation dialog box, click the Settings tab. In the Allow box, select Text Length. In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to. Enter the minimum, maximum, or specific length for the text. You can also enter a formula that returns a number value. For example, to set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1) plus 10, select less than or equal to in the Data box and enter =SUM(LEN(A1),LEN(B1),10) in the Maximum box.

Answer 4

Select the cells you want to apply this to then:

Data menu -> Validation -> Allow -> Text Length