separate numbers

  • Thread starter Thread starter T.T.
  • Start date Start date
T

T.T.

I have a column of 11 digit numbers. How can I separate the first 2 digits,
and then the 7th and 8th digits together, and store those values in
separate columns?

Thanks in advance.
Tom T.
 
Tom,

Use the MID function, for example
=MID(A1,1,2) will return the first 2 digits
=MID(A1,7,2) will return the 7th & 8th

For more info look for mid in the help menu.

Dan E
 
Look at the text functions like left and mid

=LEFT(TRIM(A1),2)

first 2

=MID(TRIM(A1),7,2)

7th and 8th

note that they are text, for numeric add 0 to the formula

=LEFT(TRIM(A1),2)+0
 
Tom, try this for the first 2 =LEFT(A1,2) and this for 7 & 8 =MID(A1,7,2)

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Data / Text To Columns / Fixed Width - Put breaks in between 2 and 3, 6 & 7, 8 & 9, then click on
the 2nd column of data (3,4,5,6) and choose 'Do not import', and also on the 4th Column of data
(9,10,11) and choose 'Do not import'. then hit OK
 
And if you want that data to be numeric and it isn't, then copy an empty cell, select all the data
and do Edit / Paste Special / Add, which will coerce it back to numeric.
 
Back
Top