MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying first digit only of a two-digit field

Posted by Roger on July 31, 2001 6:48 AM

Due to some bad design by a colleague I'm lumbered with one long column of data with each cell having two digits(?) of text in it

e.g. 'HL' with H being for High and L being for L

What I really wanted was two columns with the first column having all the first digit values and the second column all the 2nd digit values


Column 1 'H'
Column 2 'L'

Is there any easy command to do this in one go rather than have to manually change everything?



Posted by Alix on July 31, 2001 6:53 AM

Hi Roger

Say your data is in column A, starting at A1
In B1 type
In C1 type

Copy these formulae down columns B & C.
Copy all columns B & C and paste special values to get 2 columns showing the first then second digits


Posted by Mark W. on July 31, 2001 7:36 AM

> Is there any easy command to do this...

Yes. Use the Data | Text to Columns... menu command.
But first, insert a new column to the left of your
data. Next select the column containing your data,
and choose the Data | Text to Columns... menu
command. Click the "Fixed width" radio button
at Step 1 of 3, and then press [ Next> ]. At
Step 2 of 3, click in the "Data preview" field
creating a vertical line between "H" and "L", and
then press [ Finish ].