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 |

score 7 · Accepted Answer · edited Jul 25 '11 at 05:36

7

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.

edited Jul 25 '11 at 05:36

Gareth

19,080

answered Jun 16 '11 at 14:16

falter

2,097

score 5 · Answer 2 · answered Jun 16 '11 at 14:24

5

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

answered Jun 16 '11 at 14:24

Mehper C. Palavuzlar

55,953

score 1 · Answer 3 · answered Jun 16 '11 at 14:20

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.

score 0 · Answer 4 · edited Mar 21 '17 at 15:05

0

=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.

edited Mar 21 '17 at 15:05

Gareth

19,080

answered Mar 21 '17 at 15:03

Dariusz

1

score 0 · Answer 5 · edited Mar 21 '17 at 15:05

0

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

The correct syntax is:

=LEFT(CONCATENATE(A2),30)

edited Mar 21 '17 at 15:05

Gareth

19,080

answered Feb 04 '15 at 13:23

LeaYHildebrandt

1

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

5 Answers5