Remove text leading zero in text string

[Guest]

I've spent about an hour searching on how to remove leading zeros from a
column of text. I'm sure there's an easy way to do it ... just haven't found
it yet. This is a sample of the data and, as you can see, the number of
leading zeros vary as well as the length of the original and desired text

Original Desired
0000000002 2
00002309 2309
00002477327 2477327
00003584 3584
0001036 1036
00013180620620Y 13180620620Y

 

[Andy B]

Hi
If you just want to turn the text into numbers, copy a blank unused cell,
select your range and then Edit|Paste Special|Add

Andy.

 

[Guest]

I leaned in that direction too until I saw that the last of the OP's example
has a letter at the end


Peo

 

[Guest]

Hi,

Please try this one out. Selct the range in which you have the text strings.

Data>Text to Columns
Delimited>Next
In "Other" field type 0 and check the "Treat consecutive delimiters as One"
field.
Click Next and Finish.

This is will shift your result to the next column so please ensure that you
have the column free.

Regards,

 

[Larry Stiff]

Hi
The formula

=SUBSTITUTE(A1,LEFT(A1,MATCH(TRUE,--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1)>0,0)),"")

should be changed to

=SUBSTITUTE(A1,LEFT(A1,MATCH(TRUE,--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1)>0,0)),"",1)

to replace the leading zeros only.

Enter the formula with ctrl + shift & enter
Copy down

Regards

Larry

 

[Peo Sjoblom]

Good point, it will only happen when there is one leading zero and I didn't
test for that