Tough question

[SHAETY]

This may not be able to be done, but it doesn't hurt to ask.

I am copying phone numbers from a web site and pasting onto my spreadsheet. When I copy the number it looks like 1(216) 555-4847. My spreadsheet is set up so that the 1 goes in column B, the area code minus ( ) goes in column C and the phone number minus the space and dash go in column D.
Is there any type of formula that will allow me to paste into column B and the area code and phone number are automatically moved into C & D? I am not worried about deleting ( ), the space and dash. I can always do a "Find and Replace". I know that I am streaching it but this would save me soooo much time.
Hope there is a way.
Thanks,
Shaety

[Steve Yandl]

If the format is always the same (all numbers have the area code, always
have the 1 in front and the space after the area code, you could try this.

If your phone number is pasted into B1, in C1 you could have
=MID(B1,3,3)
and then in D1 you could have
=MID(B1,8,3)&MID(B1,12,4)

You would drag the fill handles on the two entered formula far enough to
handle all the rows where you've pasted numbers in column B. If the phone
number format sometimes varies, you will need to expand the formulas with
some IF functions.

Steve


"SHAETY" <[email protected]> wrote in
message news:[email protected]...
>
> This may not be able to be done, but it doesn't hurt to ask.
>
> I am copying phone numbers from a web site and pasting onto my
> spreadsheet. When I copy the number it looks like 1(216) 555-4847. My
> spreadsheet is set up so that the 1 goes in column B, the area code
> minus ( ) goes in column C and the phone number minus the space and
> dash go in column D.
> Is there any type of formula that will allow me to paste into column B
> and the area code and phone number are automatically moved into C & D?
> I am not worried about deleting ( ), the space and dash. I can always
> do a "Find and Replace". I know that I am streaching it but this would
> save me soooo much time.
> Hope there is a way.
> Thanks,
> Shaety
>
>
> --
> SHAETY
> ------------------------------------------------------------------------
> SHAETY's Profile:
> http://www.excelforum.com/member.php...o&userid=31650
> View this thread: http://www.excelforum.com/showthread...hreadid=513395
>

[Gary''s Student]

In B1 thru D1 put:

=LEFT(A1,1) =MID(A1,3,3) =RIGHT(A1,4)

--
Gary's Student


"SHAETY" wrote:

>
> This may not be able to be done, but it doesn't hurt to ask.
>
> I am copying phone numbers from a web site and pasting onto my
> spreadsheet. When I copy the number it looks like 1(216) 555-4847. My
> spreadsheet is set up so that the 1 goes in column B, the area code
> minus ( ) goes in column C and the phone number minus the space and
> dash go in column D.
> Is there any type of formula that will allow me to paste into column B
> and the area code and phone number are automatically moved into C & D?
> I am not worried about deleting ( ), the space and dash. I can always
> do a "Find and Replace". I know that I am streaching it but this would
> save me soooo much time.
> Hope there is a way.
> Thanks,
> Shaety
>
>
> --
> SHAETY
> ------------------------------------------------------------------------
> SHAETY's Profile: http://www.excelforum.com/member.php...o&userid=31650
> View this thread: http://www.excelforum.com/showthread...hreadid=513395
>
>

[George]

If the positions of the numbers are fixed you can use the text functions
left, mid, right
B1 = LEFT(A1,1)
C1 = MID(A1,3,3)
D1 = SUBSTITUTE(RIGHT(A1,8),"-","")

This can be a little tricky if for example your country code
varies from 1 digit to 2 digits. Then you need something like this

B1 = LEFT(A1,FIND("(",A1)-1)
C1 = MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
D1 = SUBSTITUTE(RIGHT(A1,8),"-","")

Copy the formulas down then...
Once your done copy and paste special > values over themselves

George

SHAETY wrote:
> This may not be able to be done, but it doesn't hurt to ask.
>
> I am copying phone numbers from a web site and pasting onto my
> spreadsheet. When I copy the number it looks like 1(216) 555-4847. My
> spreadsheet is set up so that the 1 goes in column B, the area code
> minus ( ) goes in column C and the phone number minus the space and
> dash go in column D.
> Is there any type of formula that will allow me to paste into column B
> and the area code and phone number are automatically moved into C & D?
> I am not worried about deleting ( ), the space and dash. I can always
> do a "Find and Replace". I know that I am streaching it but this would
> save me soooo much time.
> Hope there is a way.
> Thanks,
> Shaety
>
>

[kbkst via OfficeKB.com]

Gary's Student:
Thanks for you response however, I am actually starting in B2 thru D2. Does
this change the formula at all?
kbkst

Gary''s Student wrote:
>In B1 thru D1 put:
>
>=LEFT(A1,1) =MID(A1,3,3) =RIGHT(A1,4)
>
>> This may not be able to be done, but it doesn't hurt to ask.
>>
>[quoted text clipped - 11 lines]
>> Thanks,
>> Shaety

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200602/1

[kbkst via OfficeKB.com]

Steve Yandl:
Thank you so much for your response but I am coming up with a #REF. My
spreadsheet is actually B2, C2 and D2. I don't know if the formual has to
change for this. I tried putting in the B2 within the formula but it is not
working. Hope to hear from you.
kbkst

Steve Yandl wrote:
>If the format is always the same (all numbers have the area code, always
>have the 1 in front and the space after the area code, you could try this.
>
>If your phone number is pasted into B1, in C1 you could have
>=MID(B1,3,3)
>and then in D1 you could have
>=MID(B1,8,3)&MID(B1,12,4)
>
>You would drag the fill handles on the two entered formula far enough to
>handle all the rows where you've pasted numbers in column B. If the phone
>number format sometimes varies, you will need to expand the formulas with
>some IF functions.
>
>Steve
>
>> This may not be able to be done, but it doesn't hurt to ask.
>>
>[quoted text clipped - 11 lines]
>> Thanks,
>> Shaety

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200602/1

[kbkst via OfficeKB.com]

George:
I tried this but not working. I am actually starting with B2, C2, D2.
Please let me know what columns to post these in. I am not very up on the
excel spreadsheet and could really use your help.
Thanks!

George wrote:
>If the positions of the numbers are fixed you can use the text functions
>left, mid, right
>B1 = LEFT(A1,1)
>C1 = MID(A1,3,3)
>D1 = SUBSTITUTE(RIGHT(A1,8),"-","")
>
>This can be a little tricky if for example your country code
>varies from 1 digit to 2 digits. Then you need something like this
>
>B1 = LEFT(A1,FIND("(",A1)-1)
>C1 = MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
>D1 = SUBSTITUTE(RIGHT(A1,8),"-","")
>
>Copy the formulas down then...
>Once your done copy and paste special > values over themselves
>
>George
>
>> This may not be able to be done, but it doesn't hurt to ask.
>>
>[quoted text clipped - 11 lines]
>> Thanks,
>> Shaety

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200602/1

[Steve Yandl]

In every instance that you find B1 in my formula, change it to B2. Create
the formulae in C2 and D2 and drag them down.

Steve


"kbkst via OfficeKB.com" <u18755@uwe> wrote in message
news:5bfde22f1593c@uwe...
> Steve Yandl:
> Thank you so much for your response but I am coming up with a #REF. My
> spreadsheet is actually B2, C2 and D2. I don't know if the formual has to
> change for this. I tried putting in the B2 within the formula but it is
> not
> working. Hope to hear from you.
> kbkst
>
> Steve Yandl wrote:
>>If the format is always the same (all numbers have the area code, always
>>have the 1 in front and the space after the area code, you could try this.
>>
>>If your phone number is pasted into B1, in C1 you could have
>>=MID(B1,3,3)
>>and then in D1 you could have
>>=MID(B1,8,3)&MID(B1,12,4)
>>
>>You would drag the fill handles on the two entered formula far enough to
>>handle all the rows where you've pasted numbers in column B. If the phone
>>number format sometimes varies, you will need to expand the formulas with
>>some IF functions.
>>
>>Steve
>>
>>> This may not be able to be done, but it doesn't hurt to ask.
>>>
>>[quoted text clipped - 11 lines]
>>> Thanks,
>>> Shaety
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200602/1

[Ron Rosenfeld]

On Thu, 16 Feb 2006 15:47:59 -0600, SHAETY
<[email protected]> wrote:

>
>This may not be able to be done, but it doesn't hurt to ask.
>
>I am copying phone numbers from a web site and pasting onto my
>spreadsheet. When I copy the number it looks like 1(216) 555-4847. My
>spreadsheet is set up so that the 1 goes in column B, the area code
>minus ( ) goes in column C and the phone number minus the space and
>dash go in column D.
>Is there any type of formula that will allow me to paste into column B
>and the area code and phone number are automatically moved into C & D?
>I am not worried about deleting ( ), the space and dash. I can always
>do a "Find and Replace". I know that I am streaching it but this would
>save me soooo much time.
>Hope there is a way.
>Thanks,
>Shaety

Assumptions:

A. You paste your phone numbers into B2:Bn
B. There is a label in B1
C. The phone number format always has a three digit number for the area code;
a three digit number for the exchange and a four digit number.
D. There is always a non-digit between the 1 (if present), the area code, the
three digit exchange, and the four digit number.

If the above is not always met, decide what other variations could be present.

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. <alt-F11> opens the VB Editor
3. Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

4. Paste your list of numbers into B2:Bn

5. <alt-F8> opens the macro dialog box. Select PhoneNums and Run.

6. Enjoy

=============================
Option Explicit

Sub PhoneNums()
Dim c As Range
Dim p As Range
Dim pn As String, temp As String
Dim i As Long

Set p = [b2].CurrentRegion
Set p = p.Offset(1, 0).Resize(p.Rows.Count - 1, 1)

For Each c In p
With c
pn = .Text
.Value = Run([REgex.Mid], pn, "^\d(?=\D)")
.Offset(0, 1).Value = Run([REgex.Mid], pn, "\d{3}")
.Offset(0, 2).Value = Run([REgex.Mid], pn, "\d{3}", 2) _
& Run([REgex.Mid], pn, "\d{4}")
End With
Next c

End Sub
========================


--ron