removing symbols

rhody

New Member
Joined
Jun 17, 2008
Messages
6
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?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=1&substitute(substitute(substitute(substitute(a1,"(",""),")",""),"-","")," ","")
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

Try this

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

Hope this helps.
 

rhody

New Member
Joined
Jun 17, 2008
Messages
6
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

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
ABCD
1(555)333-444415553334444
2(555)222-888815552228888
3(555)333-564615553335646
4(555)333-156615553331566
5(555)333-565415553335654
Sheet1

Hope that helps.
 

rhody

New Member
Joined
Jun 17, 2008
Messages
6
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

New Member
Joined
Jun 16, 2008
Messages
18

ADVERTISEMENT

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

New Member
Joined
Jun 17, 2008
Messages
6
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:confused:

<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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

New Member
Joined
Jun 17, 2008
Messages
6
Yea Ha!!!

works like a charm:LOL:

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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top