How to set a character limit and remove characters over that limit excel 2007

Question

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 |

Answer 1

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.

Answer 2

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,"")

Answer 3

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.

Answer 4

=LEFT(A1|20)

• A1 is the field to be reduced
• 20 specifies the number of characters to be left - all others after 20 shall be removed

It can also be achieved with:

=RIGHT(A1,20)

where the preceding characters will be removed and the last 20 left in place.

Answer 5

I tried using the above solution by Falter in Excel 2010 and got an error.

The correct syntax is:

=LEFT(CONCATENATE(A2),30)