Extract Phone Number Forumula

  • Thread starter Thread starter Kip
  • Start date Start date
K

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.
 
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.
Click to expand...
 
=right(B1,12)
assumes:
phone number in B1
no extra spaces within phone number(just numbers and hyphens)

Best Regards,


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

Dave said:
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.
Click to expand...
Click to expand...
 
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.
Click to expand...

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
 
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:
Click to expand...
=3DMID(A1,9,LEN(A1)-10)

Hope this helps.

Pete
Click to expand...
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
-----------------------------------------------------------------------------------------------------------
Click to expand...
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:
Click to expand...
<bg>

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

HTH
Regards,
Howard
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top