Check number of characters in a cell (Excel)

[sbriss]

Hello,

I’m receiving database from customer to make mailing list for Fedex. I would like to write down a script just to check if any cell contains more than 35 characters and if yes, display the cell coordinate.

I’m not very familiar with excel scripting and it may be easy for some of you to wrtie this little script.

Thanks in advance

[jl1]

@sbriss

Hello,

I’m receiving database from customer to make mailing list for Fedex. I would like to write down a script just to check if any cell contains more than 35 characters and if yes, display the cell coordinate.

I’m not very familiar with excel scripting and it may be easy for some of you to wrtie this little script.

Thanks in advance

It’s been a couple months, so I don’t know if you still need this. Just in case.

If you’re using Excel, I don’t know that I’d bother scripting to get a list of the coordinates. You can use Excel functions and then filter.

TRIM: delete all leading or trailing spaces from a cell’s value
LEN: Get the length of a cell’s value
CELL (Property, [range]): Get information about a cell. Cell(“Address”) returns the cell’s coordinates.
IF (Condition, Directive if Condition is True, Directive if Condition is False)

Full function: IF(LEN(TRIM(A1))>35,CELL(“Address”,A1),“”) – Trim leading and trailing spaces from the cell’s value. If the length of the trimmed value is greater than 35, give the cell address. If not, leave a blank value.

1. Replace the two "A1"s in the function with the first cell in your address column.
2. Find your first empty column.
3. Go to the cell in that empty column corresponding to the row of your first address (e.g. if your first address, 123 Main Street, is in cell C3, and the first empty column is E, go to E3).
4. If that cell isn’t formatted to General, set it to General and refresh.
5. Paste the function into the cell.
6. Copy the function down to the end of the column.
7. Filter out blanks.

Now the cell addresses are there if you really want a list. However, at the same time, all you’re left with are rows where addresses are longer than 35 characters, so you have the option to immediately work right off the spreadsheet without manually looking up each set of cell coordinates. Saves you some scrolling. Send the spreadsheet on and you can always re-filter to find out what’s left.