Archive of Mr Excel Message Board

Back to Data in Excel archive index
Back to archive home

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

e.g.

Column 1 'H'
Column 2 'L'

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

Cheers,

ROGER

Re: Copying first digit only of a two-digit field

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

Hi Roger
Ok

Say your data is in column A, starting at A1
In B1 type
=left(A1,1)
In C1 type
=right(A1,1)

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
HTH

Alix

Re: Copying first digit only of a two-digit field

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.