Extract Phone Number Forumula

[Kip]

I have a spreadsheet that has a phone number with a data label also
and I want to find out how to use a formula to extract just the phone
number to another cell on a new spreadsheet to use for importing into
a new database. The current cell format is as follows:

[Phone: xxx-xxx-xxxx]

Anyone know how to get rid of the "phone" data label and just be left
with the phone number? Thanks.

 

[Dave Peterson]

I would just select the range and do a couple of edit|replaces.

Copy the range to its new home on the other worksheet first.

Select the range
Edit|Replace
what: [Phone:_ (where _ is the space character)
with: (Leave blank)
replace all

And then get that trailing ]
With the range still selected
Edit|Replace
what: ]
with: (leave blank)
replace all

I have a spreadsheet that has a phone number with a data label also
and I want to find out how to use a formula to extract just the phone
number to another cell on a new spreadsheet to use for importing into
a new database. The current cell format is as follows:

[Phone: xxx-xxx-xxxx]

Anyone know how to get rid of the "phone" data label and just be left
with the phone number? Thanks.

 

[Pete_UK]

Try this:

=MID(A1,9,LEN(A1)-10)

Hope this helps.

Pete

 

[helene and gabor]

=right(B1,12)
assumes:
phone number in B1
no extra spaces within phone number(just numbers and hyphens)

Best Regards,


Gabor Sebo

 

[Dave Peterson]

Ps. I assumed that you wanted to keep the hyphens, too.

I would just select the range and do a couple of edit|replaces.

Copy the range to its new home on the other worksheet first.

Select the range
Edit|Replace
what: [Phone:_ (where _ is the space character)
with: (Leave blank)
replace all

And then get that trailing ]
With the range still selected
Edit|Replace
what: ]
with: (leave blank)
replace all

I have a spreadsheet that has a phone number with a data label also
and I want to find out how to use a formula to extract just the phone
number to another cell on a new spreadsheet to use for importing into
a new database. The current cell format is as follows:

[Phone: xxx-xxx-xxxx]

Anyone know how to get rid of the "phone" data label and just be left
with the phone number? Thanks.

 

[L. Howard Kittle]

And the hard way...<bg>

=RIGHT(A1,LEN(A1)-FIND(": ",A1)-1)

HTH
Regards,
Howard

 

[Ron Rosenfeld]

I have a spreadsheet that has a phone number with a data label also
and I want to find out how to use a formula to extract just the phone
number to another cell on a new spreadsheet to use for importing into
a new database. The current cell format is as follows:

[Phone: xxx-xxx-xxxx]

Anyone know how to get rid of the "phone" data label and just be left
with the phone number? Thanks.

And another method:

Data/Text to columns/Delimited

Select <space> and <other> as the delimiters. In the Other box, enter the "]"

You can then select to not import the column with [Phone:

--ron

 

[Gloops]

Hello,

Well, this is what can happen when you still did not thank people for
their answers after two years.

What is the link with the question ?
What has a commercial tool to do with Mid$(A1, 10) ?

It appears Ron Rosenfeld already has a commercial tool, it is called
Ms-Excel.
On the other side, he does not seem to have remembered where he asked
his question
_________________________________________________
Tech guru wrote, on 28th Feb. 2012 21:00 UTC + 1 :

Hi, There are commercial tools which can help in Extracting phone numbers from Excel, Word , PDF multiple files and some of examples are here

http://www.technocomsolutions.com/file-phone-number-extractor.htm

I have a spreadsheet that has a phone number with a data label also
and I want to find out how to use a formula to extract just the phone
number to another cell on a new spreadsheet to use for importing into
a new database. The current cell format is as follows:

[Phone: xxx-xxx-xxxx]

Anyone know how to get rid of the "phone" data label and just be left
with the phone number? Thanks.

On Saturday, April 24, 2010 4:43 PM Dave Peterson wrote:
I would just select the range and do a couple of edit|replaces.

Copy the range to its new home on the other worksheet first.

Select the range
Edit|Replace
what: [Phone:_ (where _ is the space character)
with: (Leave blank)
replace all

And then get that trailing ]
With the range still selected
Edit|Replace
what: ]
with: (leave blank)
replace all



Kip wrote:

=3DMID(A1,9,LEN(A1)-10)

Hope this helps.

Pete

On Saturday, April 24, 2010 4:52 PM helene and gabor wrote:
=right(B1,12)
assumes:
phone number in B1
no extra spaces within phone number(just numbers and hyphens)

Best Regards,


Gabor Sebo
-----------------------------------------------------------------------------------------------------------

On Saturday, April 24, 2010 4:54 PM Dave Peterson wrote:
Ps. I assumed that you wanted to keep the hyphens, too.

Dave Peterson wrote:

<bg>

=RIGHT(A1,LEN(A1)-FIND(": ",A1)-1)

HTH
Regards,
Howard

Data/Text to columns/Delimited

Select<space> and<other> as the delimiters. In the Other box, enter the "]"

You can then select to not import the column with [Phone:

--ron