TheCandyman
Technical User
How do i remove characters from Excel like such:
(123) 456-7890 changed to 1234567890
anyone??
(123) 456-7890 changed to 1234567890
anyone??
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Remove character from Excel
- Thread starter TheCandyman
- Start date
- Status
With your data in A1:A100, put the following in B1 and copy down.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""
,"-","","","","(",""
When done, simply select B1:B100, copy and paste special as values.
Regards
Ken.................
----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""
,"-","","","","(",""When done, simply select B1:B100, copy and paste special as values.
Regards
Ken.................
----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
If you're doing a whole column full of phone numbers you could always do 3 find and replaces?
The first one would be
Find: (
Replace with:
and don't put anything in the replace with field
Then Replace All
The second one would be
Find: )<space>
Replace with:
and again don't put anything in the replace with field
Then Replace All
The third one would be
Find: -
Replace with:
and yet again don't put anything in the replace with field
Then Replace All
This is good if you've got several phone numbers that need to have all the special characters removed.
Just my opinion...a simplified way of doing.
The first one would be
Find: (
Replace with:
and don't put anything in the replace with field
Then Replace All
The second one would be
Find: )<space>
Replace with:
and again don't put anything in the replace with field
Then Replace All
The third one would be
Find: -
Replace with:
and yet again don't put anything in the replace with field
Then Replace All
This is good if you've got several phone numbers that need to have all the special characters removed.
Just my opinion...a simplified way of doing.
- Status
