Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
How do I separate a text value with dashes?
Hello,
I have a column (called Tract Number) that contains a column with a 11 character number (BTW, it really isn't a number, if that helps), such as this one: 7-5-029-001. The number will ALWAYS have this format, and will ALWAYS contain 11 characters. The first number (7 in this case) is for Township, the second (5) for Range, the third (029) for Section, and the fourth (001) for Tract. I have 4 new columns with Township, Range, Section, and Tract to the immediate right of the Tract Number column. Here is my question: How can I break up the 11 characters so that they will automatically populate the appropiate columns? TIA for your reply. |
#2
|
|||
|
|||
One way
Have a look at DataText to Columns. You test is delimited and the delimiter is other "-" select your destination cell HTH "Phil" wrote: Hello, I have a column (called Tract Number) that contains a column with a 11 character number (BTW, it really isn't a number, if that helps), such as this one: 7-5-029-001. The number will ALWAYS have this format, and will ALWAYS contain 11 characters. The first number (7 in this case) is for Township, the second (5) for Range, the third (029) for Section, and the fourth (001) for Tract. I have 4 new columns with Township, Range, Section, and Tract to the immediate right of the Tract Number column. Here is my question: How can I break up the 11 characters so that they will automatically populate the appropiate columns? TIA for your reply. |
#3
|
|||
|
|||
Assume the number you want to separate is in cell a1 and you want to
separate it into b1, c1, d1 and e1. The following formulas will do it: in b1, use =left (a1,1) in c1, use =mid (a1,3,1) in d1, use =mid (a1,5,3) in e1, use =right(a1,3) Fill these columns down and you'll distribute all the numbers in column a across b, c, d, and e. Chuck |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Text Wrapping | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |