separate numbers

[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.

 

[Dan E]

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

 

[Peo Sjoblom]

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

 

[Paul B]

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 **

 

[Ken Wright]

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

 

[Ken Wright]

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.