removing symbols

[rhody]

Hello:

I have lists of phone numbers with parenthases and hyphens. like (555) 333-4444

I would like to remove all symbols and leave just the numbers.


I would alsolike to add the number 1 in the first position.

so (555)333-4444 would end up 15553334444

any ideas?

 

[Scott Huish]

=1&substitute(substitute(substitute(substitute(a1,"(",""),")",""),"-","")," ","")

 

[Jonmo1]

Welcome to the board...

Try this

=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")

Hope this helps.

 

[rhody]

Thanks for the quick reply

Now I need help in how to insert this formula!

I'm sure the formula is right as I haven't used xcel in a bout a year and I guess I forgot more than I thought!:

I tried inserting it but xcel said it created a circular reference. I tried to use the circular reference toolobar with no success.

also I saw that one of my original databases was listed as a "comma" databae in the icon, is that an issue?

I tried starting with a whole new trial database with three or four numbers and no luck.

Please try and explain

OR if you really pity me and you feel very chartitable, maybe someone could make a quick database using 5 or 6 phone numbers, insert the formula and email it to me? conimicut1@yahoo.com that way I can see it done?

Don't laugh! If you have a car or boat question I can help you (maybe).

 

[schielrn]

Welcome to the Board!

It needs to be placed in an empty cell and A1 then needs to be changed to whatever cell contains the phone number. Then you can copy the cell and go to edit-->paste special and past values over the old phone number with the dashes.

Book1
	A	B	C	D
1	(555)333-4444	15553334444
2	(555)222-8888	15552228888
3	(555)333-5646	15553335646
4	(555)333-1566	15553331566
5	(555)333-5654	15553335654
Sheet1

Hope that helps.

 

[rhody]

yes that helped!

But (don't groan!) how do I apply it to the remaining phone numbers in the collumn? paste the formula and change it for each cell? I have collumns of a few hundred phone numbers

I guess I could make a database with just the formulas copied, save it as a master and then paste the phone numbers in

 

[Muteki]

Click on A1 and hover over the bottom right corner, a small black plus sign should appear. Simply drag that down as far as you need it and it will auto update the formula for each row

 

[rhody]

First I must say thanks to everyone, your replies are so fast!

I tried the +right corner, it kinda works, but it adds extra ones to the cells above , so I get this

<TABLE style="WIDTH: 65pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=86 border=0 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=86 height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")," ","")'>111111111114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"(",""),")",""),"-","")," ","")'>11111111114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"(",""),")",""),"-","")," ","")'>1111111114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,"(",""),")",""),"-","")," ","")'>111111114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"(",""),")",""),"-","")," ","")'>11111114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A7,"(",""),")",""),"-","")," ","")'>1111114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"(",""),")",""),"-","")," ","")'>111114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A9,"(",""),")",""),"-","")," ","")'>11114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A10,"(",""),")",""),"-","")," ","")'>1114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A11,"(",""),")",""),"-","")," ","")'>114012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:fmla='=1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A12,"(",""),")",""),"-","")," ","")'>14012222807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>(401)222-2807</TD></TR></TBODY></TABLE>

 

[schielrn]

How is your data laid out? You cannot put the formula above it and drag up or below it and drag down. You need to put it in a cell next to teh phone number. Then you can drag down teh cell next to it or copy and paste it next to another number on the sheet?

 

[rhody]

Yea Ha!!!

works like a charm

Again you guys are great!!!!!!!!!!!!!!!!!

I always say ask the experts (and then listen to them)!