Formula for first 4 digits

[Wrecker1]

Is there a formula to copy the first four digits in a column to the next column?

in c2..... = ****(b2)?

Not sure how to tell it I want just the first 4 digits.

 

[Andrew Poulsom]

Try:

=LEFT(B2,4)

 

[cass108]

Adding the VALUE function returns the digits as numeric values instead of a text string:

=VALUE(LEFT(b2,4))

 

[IML]

On 2002-09-17 10:57, cass108 wrote:
Adding the VALUE function returns the digits as numeric values instead of a text string:

=VALUE(LEFT(b2,4))

as would
=LEFT(A4,4)+0

or even dressed up a little more for error trapping:
=IF(ISNUMBER(LEFT(A4,4)+0),LEFT(A4,4)+0,LEFT(A4,4))

 

[Mark W.]

This can also be done using the Data | Text to Columns... menu command after selecting your data...

1. At Step 1 of 3 choose "Fixed width" and press [ Next> ]

2. At Step 2 of 3 click in the data preview window to insert a break line after the 4th character from the left, and press [ Next> ]

3. At Step 3 of 3 set the 1st parsed column's data format as desired, set the 2nd column's format to "Do not import column (skip)", set the destination to the cell address of the 1st cell in the column where you want the data to appear, and press [ Finish ].

Voila! ...and, "Look ma', no formulas needed!"
This message was edited by Mark W. on 2002-09-17 12:23

 

[IML]

Good to know...When I first looked at this, I didn't think it would work since I expected numbers to appear as nubmers and be right justified in text to columns. I guess text to columns is correctly named!

 

[Mark W.]

Yeah, it beats the hell out of the old Data | Parse command.