convert a text phone number to numeric phone number

trixie · Oct 17th, 2003, 09:08 PM

I am trying to upload a text file from another program that formats the telephone number as (xxx) xxx-xxxx and Excel keeps reading it as text rather than as a numeric value. I can't change the format no matter what I try. The end result I am trying to achieve is to load this file into access and compare the telephone number against another table. Access does not recognize the text format and does not see that the excel table numbers are equal.
Help!

Joe4 · Oct 17th, 2003, 09:14 PM

If you want just as a straight number, first do a Find & Replace to get rid of all parends, dashes, and spaces. Then format the column as General or Numbers. Then do a Text to Columns (from the Data drop down menu), switching the data to General format.

rrdonutz · Oct 17th, 2003, 09:19 PM

Here's a formula solution, given your format structure of (xxx) xxx-xxxx:

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

Ugly, but it works. Jmiskey's is probably a better approach, though.

--Tom

trixie · Oct 17th, 2003, 09:21 PM

That was so simple I feel really silly now! Thank you so much, you saved my poor aching head! Hope I can return the favor someday!

twistinginthewind · Oct 17th, 2003, 09:23 PM

how about:

ph_num=val(mid(data,2,3) & mid(data,7,3) & mid(data,11,4))

where data = orginal data from file
also all in coming data would have to be same format (xxx) xxx-xxxx

Smitty · Oct 17th, 2003, 09:24 PM

Welcome to the Board!

Quote:

Hope I can return the favor someday!

Wait til you get the bill!

Smitty

Oct 17th, 2003, 09:08 PM	#1
trixie Join Date: Oct 2003 Location: Florida Posts: 7	convert a text phone number to numeric phone number I am trying to upload a text file from another program that formats the telephone number as (xxx) xxx-xxxx and Excel keeps reading it as text rather than as a numeric value. I can't change the format no matter what I try. The end result I am trying to achieve is to load this file into access and compare the telephone number against another table. Access does not recognize the text format and does not see that the excel table numbers are equal. Help!

Oct 17th, 2003, 09:14 PM	#2
Joe4 MrExcel MVP Moderator Join Date: Aug 2002 Posts: 16,457	Re: convert a text phone number to numeric phone number If you want just as a straight number, first do a Find & Replace to get rid of all parends, dashes, and spaces. Then format the column as General or Numbers. Then do a Text to Columns (from the Data drop down menu), switching the data to General format. __________________ TIPS FOR FINDING EXCEL SOLUTIONS 1. Use the built-in Help that comes with Excel/Access 2. Use the Search functionality on this board 3. A lot of VBA code can be acquired by using the Macro Recorder.

Oct 17th, 2003, 09:19 PM	#3
rrdonutz Join Date: Jan 2003 Location: Round Rock, Texas Posts: 564	Re: convert a text phone number to numeric phone number Here's a formula solution, given your format structure of (xxx) xxx-xxxx: =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")," ",""),"-","") Ugly, but it works. Jmiskey's is probably a better approach, though. --Tom

Oct 17th, 2003, 09:21 PM	#4
trixie Join Date: Oct 2003 Location: Florida Posts: 7	Re: convert a text phone number to numeric phone number That was so simple I feel really silly now! Thank you so much, you saved my poor aching head! Hope I can return the favor someday!

Oct 17th, 2003, 09:23 PM	#5
twistinginthewind Join Date: Jun 2003 Location: San Francisco Bay Area Posts: 110	how about: ph_num=val(mid(data,2,3) & mid(data,7,3) & mid(data,11,4)) where data = orginal data from file also all in coming data would have to be same format (xxx) xxx-xxxx __________________ Shawn

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode