Home |
Search |
Today's Posts |
#1
|
|||
|
|||
breaking up a phone number
I need to break up a phone number into three different columns. I have column A that contains the phone number xxx-xxx-xxxx, I need to split that up into three different columns. Column B would equal the area code... column C would equal the Prefix.. and column D would equal the digits.. any suggestions on how to accomplish this? thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php...o&userid=25877 View this thread: http://www.excelforum.com/showthread...hreadid=392596 |
#2
|
|||
|
|||
Use the Left, Mid and right functions If your phone number is in A2 then in B2 put: =LEFT(A2, 3) in C2 put: =MID(A2, 5, 3) in D2 put: =RIGHT(A2, 4) you can then copy B2 to D2 and fill down HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=392596 |
#3
|
|||
|
|||
Perfect! Thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php...o&userid=25877 View this thread: http://www.excelforum.com/showthread...hreadid=392596 |
#4
|
|||
|
|||
Use Data Text to Columns...
This will split the fields very nicely. -- Gary's Student "maximus73" wrote: I need to break up a phone number into three different columns. I have column A that contains the phone number xxx-xxx-xxxx, I need to split that up into three different columns. Column B would equal the area code... column C would equal the Prefix.. and column D would equal the digits.. any suggestions on how to accomplish this? thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php...o&userid=25877 View this thread: http://www.excelforum.com/showthread...hreadid=392596 |
#5
|
|||
|
|||
Hi
Try this with the number in A1 In B1 =MID(A1,1,3) In C1 =MID(A1,5,3) In D1 =MID(A1,9,4) Select B1:D1 and copy down -- Regards Ron de Bruin http://www.rondebruin.nl "maximus73" wrote in message ... I need to break up a phone number into three different columns. I have column A that contains the phone number xxx-xxx-xxxx, I need to split that up into three different columns. Column B would equal the area code... column C would equal the Prefix.. and column D would equal the digits.. any suggestions on how to accomplish this? thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php...o&userid=25877 View this thread: http://www.excelforum.com/showthread...hreadid=392596 |
#6
|
|||
|
|||
Hi,
Select the entire data in column A and do the following: Data -- Text to Columns -- make sure 'Delimited' is checked and click 'Next' -- select the 'Other' button and enter a hyphen mark in the box by its side and click 'Next' -- 'Finish'. Regards, B.R. Ramachandran "maximus73" wrote: I need to break up a phone number into three different columns. I have column A that contains the phone number xxx-xxx-xxxx, I need to split that up into three different columns. Column B would equal the area code... column C would equal the Prefix.. and column D would equal the digits.. any suggestions on how to accomplish this? thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php...o&userid=25877 View this thread: http://www.excelforum.com/showthread...hreadid=392596 |
#7
|
|||
|
|||
Another option (for other readers of this thread) is to use the Text to Columns feature. Insert three blank columns (if needed) next to your phone number column to receive the data. Highlight the range of phone numbers and click DataText to Columns... Choose "Delimited" and click Next Set the delimiter as "Other" and enter the dash "-" and click Finish. Now all of your phone numbers are separated into three columns. At this point, you could (if desired) delete Column A. HTH Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=392596 |
#8
|
|||
|
|||
Hi
We can do in different ways column b = left(A,3) column c = mid(A,5,3) column d = right(A,4) "maximus73" wrote: I need to break up a phone number into three different columns. I have column A that contains the phone number xxx-xxx-xxxx, I need to split that up into three different columns. Column B would equal the area code... column C would equal the Prefix.. and column D would equal the digits.. any suggestions on how to accomplish this? thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php...o&userid=25877 View this thread: http://www.excelforum.com/showthread...hreadid=392596 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Phone number | Excel Discussion (Misc queries) | |||
Phone Number Formats | Excel Discussion (Misc queries) | |||
Maximum number of rows? | Excel Discussion (Misc queries) | |||
duplicate number | Excel Discussion (Misc queries) | |||
Phone Dialer | Excel Discussion (Misc queries) |