Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have to provide a CSV file for a telephone system with names and numbers on for a directory. The numbers cannot contain any spaces, and the names must be 20 characters max.

I have a list of names/numbers, but the numbers have spaces and the names are full names. Is there a way to remove characters over the 20 character limit, and remove the blank spaces from the numbers column?

My current CSV file looks like this:

+------------ NAME ------------+--- NUMBER ---+
| johnathan ferguson suppliers | 01234 567899 |
| johnathan ferguson suppliers | 01234 567899 |
share|improve this question
up vote 4 down vote accepted

Found the answer for character limit:


Where A1 is the column with the full text in, and 20 is the character limit now set.

answer for removing spaces:

=SUBSTITUTE(C3," ", "")

Where C3 is your phone number with spaces.

share|improve this answer

For the name column (Let's call it A), you can enter the following formula in a new column:


For the number column (Let's call it B), you can enter the following formula in a new column:

share|improve this answer

If you don't want to go to the trouble of writing a macro, simply highlight the Number column and press CTRL + F to bring up the Find command. Click the Replace tab then enter a space in the find box and leave the replace with box empty. Click Replace All and all the spaces in your Number Column will be removed. Looks like you have the character limit problem taken care of now.

share|improve this answer

Tried using the above solution by Falter in Excel 2010 and got an error. Found the correct syntax to be:


share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .