Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

up vote 3 down vote accepted

Found the answer for character limit:

=LEFT(CONCATENATE(A1),20)

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

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

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

=LEFT(A2,20)

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

=REPLACE(B2,6,1,"")
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.