LEFT() Function with leading zeros

detriez

Board Regular
Joined
Sep 13, 2011
Messages
172
Office Version
  1. 365
Platform
  1. Windows
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


09710209710202
09719909719900
09719909719901
09719909719902
10110010110000
10110110110100
10110110110101
10110110110102
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

detriez

Board Regular
Joined
Sep 13, 2011
Messages
172
Office Version
  1. 365
Platform
  1. Windows
Sorry.. I DO want the leading zero when it exists
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
So according to your description and sample, it's already doing it.

Can you show your LEFT formula?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff 1.xlsm
JK
1
209710209710202
309719909719900
409719909719901
509719909719902
610110010110000
710110110110100
810110110110101
910110110110102
Main
Cell Formulas
RangeFormula
J2:J9J2=LEFT(TEXT(K2,"00000000"),6)
 
Solution

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
566
Office Version
  1. 2011
Platform
  1. MacOS
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

Board Regular
Joined
Sep 13, 2011
Messages
172
Office Version
  1. 365
Platform
  1. Windows
Thanks All!

Fluff, Your formula worked great.. I was actually trying to force the apostrophe (TEXT) with this but yours worked great
VBA Code:
="'"& LEFT(B2,6)

Thanks again everyone
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,909
Messages
5,721,458
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top