| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find And Replace a Dash
How do I do this? Look at Phone Number...I need the dash.
Actual 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 9544809085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 3042355659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 9898332265 This is what I would like to finish with. I have hundreds of these. Results 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 954-480-9085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 304-235-5659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 989-833-2265 Thank You Steve |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find And Replace a Dash
Hi,
Assuming you first entry is in A1, put this in b1 =LEFT(A1,LEN(A1)-7)&"-"&MID(A1,LEN(A1)-7,3)&"-"&RIGHT(A1,4) Double click the fill handle to fill down, Mike "Maverick50" wrote: How do I do this? Look at Phone Number...I need the dash. Actual 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 9544809085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 3042355659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 9898332265 This is what I would like to finish with. I have hundreds of these. Results 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 954-480-9085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 304-235-5659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 989-833-2265 Thank You Steve |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find And Replace a Dash
Hi,
Try this =TEXT(A2,"000\-000\-0000") change the cell reference as it suits you. -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Maverick50" wrote: How do I do this? Look at Phone Number...I need the dash. Actual 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 9544809085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 3042355659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 9898332265 This is what I would like to finish with. I have hundreds of these. Results 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 954-480-9085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 304-235-5659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 989-833-2265 Thank You Steve |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find And Replace a Dash
GGGGreat....... Cut and paste and away I went!!!!!!
P.S. I do this for my GPS...My own benifit. "Mike H" wrote: Hi, Assuming you first entry is in A1, put this in b1 =LEFT(A1,LEN(A1)-7)&"-"&MID(A1,LEN(A1)-7,3)&"-"&RIGHT(A1,4) Double click the fill handle to fill down, Mike "Maverick50" wrote: How do I do this? Look at Phone Number...I need the dash. Actual 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 9544809085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 3042355659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 9898332265 This is what I would like to finish with. I have hundreds of these. Results 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 954-480-9085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 304-235-5659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 989-833-2265 Thank You Steve |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find And Replace a Dash
That would be OK if the cell contained only the phone number, but it doesn't
work with the sort of text string which the OP gave as examples. -- David Biddulph "Suleman Peerzade" wrote in message ... Hi, Try this =TEXT(A2,"000\-000\-0000") change the cell reference as it suits you. -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Maverick50" wrote: How do I do this? Look at Phone Number...I need the dash. Actual 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 9544809085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 3042355659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 9898332265 This is what I would like to finish with. I have hundreds of these. Results 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 954-480-9085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 304-235-5659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 989-833-2265 Thank You Steve |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find And Replace a Dash
Hi,
You could also try something like this: =LEFT(A1,LEN(A1)-10)&TEXT(RIGHT(A1,10),"000\-000\-0000") Dave "David Biddulph" wrote: That would be OK if the cell contained only the phone number, but it doesn't work with the sort of text string which the OP gave as examples. -- David Biddulph "Suleman Peerzade" wrote in message ... Hi, Try this =TEXT(A2,"000\-000\-0000") change the cell reference as it suits you. -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Maverick50" wrote: How do I do this? Look at Phone Number...I need the dash. Actual 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 9544809085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 3042355659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 9898332265 This is what I would like to finish with. I have hundreds of these. Results 4915 N Federal Highway<brLighthouse Point,, FL 33064<brPhone: 954-480-9085 11 West 2ND Avenue<br<brWilliamson,, WV 25661<brPhone: 304-235-5659 10673 W Street<br Charles Road<brSumner, MI 48889<brPhone: 989-833-2265 Thank You Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replace Comma's with Dash | Excel Worksheet Functions | |||
| Replace Comma with Dash | Excel Worksheet Functions | |||
| replace dash in a number sequence | Excel Worksheet Functions | |||
| Replace Error with a Dash | Excel Worksheet Functions | |||
| Find an empty cell and put a dash in it? | Excel Worksheet Functions | |||
Linear Mode
