LEFT() Function with leading zeros

[detriez]

My data has 8 digits some of which have a leading zero
I'm trying to Use the LEFT Function to get the first 6 digits including the leading zero when it exists (see below)
I can force it by adding an apostrophe when typing the values but, how can I do it for thousands of records?
I tried all of the formats and custom formats I could come up with to no avail

097102	09710202
097199	09719900
097199	09719901
097199	09719902
101100	10110000
101101	10110100
101101	10110101
101101	10110102

 

[jtakw]

Hi,

Are you saying you Want or Don't want the leading zero?

 

[detriez]

Sorry.. I DO want the leading zero when it exists

 

[jtakw]

So according to your description and sample, it's already doing it.

Can you show your LEFT formula?

 

[Fluff]

How about

+Fluff 1.xlsm
	J	K
1
2	097102	09710202
3	097199	09719900
4	097199	09719901
5	097199	09719902
6	101100	10110000
7	101101	10110100
8	101101	10110101
9	101101	10110102
Main

Cell Formulas
Range	Formula
J2:J9	J2	=LEFT(TEXT(K2,"00000000"),6)

 

[TAPS_MikeDion]

I'm not sure if this is exactly what you're looking for, but if you can change to a custom format by selecting the column (A, B, C or whatever) and from the menu up top you select Format, Number, More Formats, Custom number format, then type eight 0's. If you then go to a cell and type the formula "=LEFT(A1,6)" you'll get the leading 0's.

Hope this helps.

 

[detriez]

Thanks All!

Fluff, Your formula worked great.. I was actually trying to force the apostrophe (TEXT) with this but yours worked great

="'"& LEFT(B2,6)

Thanks again everyone

 

[Fluff]

You're welcome & thanks for the feedback.