Adding in Front and Bring Together

**textboom** · 09-08-2008, 03:07 PM

I had a customer send me a bunch of phone numbers in word, I copied and pasted them one per line in excel.

Here is the problem (2 parts)

They were sent in the following format

215 555 1212
215 555-1213
Etc..

I need to:
1. Eliminate all the spaces and just have it be one long number
2. I need to add a 1 before the number

The list is about 500 long so doing this manually would take forever... Can someone please help..

TY

**skatmandu2002** · 09-08-2008, 03:11 PM

Hiya,

Assuming that the numbers are from A1 downwards, in cell B1 type:
=1&substitute(A1," ","")

This will put the 1 in front and then the substitute function changes all spaces to nothing.

Hope that helps

edit: oh, and then autofill down column B

**NBVC** · 09-08-2008, 03:12 PM

Try in an adjacent column:

=1&REPLACE(REPLACE(A1,4,1,""),7,1,"")

where A1 houses first phone number to convert.

Then to replace original copy the entire new column go to the first cell in the original column. Go to Edit|Paste Special and select Values.

Click Ok.

Now you can delete the added column

My formula assumes you can have spaces and/or dashes between the digits...

**Cheeky Charlie** · 09-08-2008, 04:00 PM

NBVC is there a cunning reason for the complexity of your formula... or is skatmandu's just better on this occassion?

**NBVC** · 09-08-2008, 04:07 PM

Originally Posted by Cheeky Charlie

NBVC is there a cunning reason for the complexity of your formula... or is skatmandu's just better on this occassion?

My formula is meant to replace the spaces or dashes at the 4th and 7th positions as per samples given by OP.

The other suggestion only replaces spaces...the dashes will remain.

I could've use nested Substitute() function too

**skatmandu2002** · 09-08-2008, 04:13 PM

Either option will do, mine can be adjusted to include dashes or other characters, but if the spaces/dashes/etc are always gonna be in the same place NVBC's option will be best