Remove leading zeros

[Machel]

We are uploading data from another application to a .txt file and then
opening the .txt in Excel 2003. The data is employee numbers, which we are
copying and pasting into our learning management system. In the LMS we need
the number without the leading zeros. Formating as number does not work. Do
you know a formula we could use to remove the zeros when the number of
leading zeros varies? This is an example of the data:
0026278,
0067405,
0000042,
0108500,

 

[Bernard Liengme]

Try =VALUE(A1)
best wishes

 

[Machel]

Thank you for your response! That did not work - I think because of the comma
at the end of each number - which we do need.

 

[FSt1]

hi
are all the emp number the same length????
try this....
=VALUE(LEFT(A7,7))

regards
FSt1

 

[Jim Thomlinson]

This is from a previous post...
******************************
Try this array formula** :

=MID(A1,MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>"0",0),255)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
***************************************
Note that the function is volatile so once you have removed the zeros you
may want to copy and paste sepcial values to improve the calculation
efficiency...

 

[Bernard Liengme]

Try =VALUE(SUBSTITUTE(A1,",",""))&","

 

[Rick Rothstein \(MVP - VB\)]

Another possibility...

=--LEFT(A1,LEN(A1)-1)&","

Rick

 

[Rick Rothstein \(MVP - VB\)]

By the way, are **all** your entries always 7 digits followed by a comma? If
so, the formula I posted can be simplified...

=--LEFT(A1,7)&","

Rick

 

[Machel]

I am not stuck anymore - now I have a choice of solutions. Thanks so much!!

 

[Saruman]

Alternately, change the .txt extension to .csv and double click the file.

Excel will then open the file without the leading zeroes.