Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number between a dash
Hello,
Is there a way to extract numbers from a cell? Basically I have a list of APN numbers, sometimes they include letters but they are always broken with a dash. I need to extract the three different sections of the APN number into 3 different cells. For example APN number: 123A-456-789 Needs to be broken up into the following cells as follows: B1: 123A C1: 456 D1: 789 As i mentioned earlier numbers and sizes can change, the only constant is the dash. Thanks for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number between a dash
datatext to columnsdelimitedother -
-- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Hello, Is there a way to extract numbers from a cell? Basically I have a list of APN numbers, sometimes they include letters but they are always broken with a dash. I need to extract the three different sections of the APN number into 3 different cells. For example APN number: 123A-456-789 Needs to be broken up into the following cells as follows: B1: 123A C1: 456 D1: 789 As i mentioned earlier numbers and sizes can change, the only constant is the dash. Thanks for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number between a dash
You can try "Text to Columns" under Data on the menu bar.
On the first screen of Text to Columns, select "Delimited". On the 2nd screen, check the box (and uncheck all other boxes under Delimiters) "Other:", and type in a dash the box next to Other:. On the third screen, you can define the data format (Text, Date, etc.) for each delimited column. I hope that helps. "Eric" wrote: Hello, Is there a way to extract numbers from a cell? Basically I have a list of APN numbers, sometimes they include letters but they are always broken with a dash. I need to extract the three different sections of the APN number into 3 different cells. For example APN number: 123A-456-789 Needs to be broken up into the following cells as follows: B1: 123A C1: 456 D1: 789 As i mentioned earlier numbers and sizes can change, the only constant is the dash. Thanks for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract number between a dash
DataText to columnsdleimited by other. Enter a hyphen and NextNext
Column Data FormatText..........this to prevent dropping of leading zeros, if any) Finish. Gord Dibben MS Excel MVP On Wed, 23 Jan 2008 15:01:01 -0800, Eric wrote: Hello, Is there a way to extract numbers from a cell? Basically I have a list of APN numbers, sometimes they include letters but they are always broken with a dash. I need to extract the three different sections of the APN number into 3 different cells. For example APN number: 123A-456-789 Needs to be broken up into the following cells as follows: B1: 123A C1: 456 D1: 789 As i mentioned earlier numbers and sizes can change, the only constant is the dash. Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to put a dash- in my formula to separate a number&name | Excel Worksheet Functions | |||
How do I take out a dash in a number like 827-6541 in a XL list | Excel Worksheet Functions | |||
Extract number from text/number string.. | Excel Discussion (Misc queries) | |||
Replacing dash in item number | Excel Discussion (Misc queries) | |||
understanding dash dash in a excel formula | Excel Worksheet Functions |