Excel How do I remove spaces from Excel cells

[Moomin1010]

Hi all this is my first post on Pc Review so I hope i've posted in the correct forum?

I am currently working on a spread sheet containing telephone numbers. I need to upload them on my telephone system, but in order to do so, all spaces in the telephone number cells must be omitted. So that I dont have to go through 500 + cells mannually to omit the space between the area code and telephone number, I'm hoping someone can advise me if there is an easier way of doing this??

I only have a basic knowledge of Excel so assistance in 'dummy' terms would be most appreciated!

Many thanks for reading

Moomin

 

[Ian]

Without seeing the exact spreadsheet I'm not sure if this will work, but if it is just a spreadsheet full of numbers then you should be able to do a "find and replace" (on the edit menu). Search for any instance of a space and then don't type anything in the replacement box - basically this will delete any spaces.

If you have text (such as names) on the same sheet, copy just the column of numbers into a new sheet and then perform the replacement. Once it's done, paste it back

 

[Moomin1010]

Ian,

I've just carried out the find and replace but annoyingly its also removed all 0's from the start of the number!

Any ideas?

Cheers,

Claire.

 

[SlimJim]

Ian,

I've just carried out the find and replace but annoyingly its also removed all 0's from the start of the number!

This happened because removing the spaces induces Excel into treating them as numbers and hence no leading 0's. Your first step, I believe, should be to select all the cells and then set the General Format as Text this way no automatic number formatting will be done.

 

[Ian]

I think that is down to a formatting problem with excel, you might need to change the cell type to "text".

Before you do the find and replace, highlight the numbers column and right click - then select "format cells" and select "text". This should keep the 0's in front

 

[Moomin1010]

Thanks for the feedback!

I've formatted the coumn to 'text' however it's still removing the 0's?

 

[Ian]

I'm all out of ideas, but someone else here might know why it does that.

Actually... you could try pasting the column into wordpad and do the find/replace in there, and then paste it back into Excel. That might work?

 

[SlimJim]

This may not be the best solution as it will increase the size of the sheet. It appears that you have the numbers in one column. Insert another column alongside it. Suppose the original column is A and the new one is B. In cell B1 put the formula =SUBSTITUTE(A1," ",""). Now Edit Copy that cell then select the whole of the B column (to the end of the list of phone numbers) and Edit>Paste. In the quick test I have done this does the job and you get a column formatted the way that you want.

 

[jpatey]

You could try changing the formatting to General (instead of text) but it may not work. When I run into this problem, I click inside the individual cell and enter an apostrophe at the beginning of the cell text or numbers. This keeps the zeros visible. An example of this: '012345678 will return the results 012345678 (After you enter the apostrophe and hit enter, the apostrophe will not be viewed in the worksheet but you will see the zero at the beginning of the line).

If you have multiple instances of this problem, you could correct the first cell and then choose the next cell (or rows and columns) and click F4 (to repeat your previous actions). F4 should work for all of the remaining cells.

Jolene