Remove text leading zero in text string
 

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

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.

"Peo Sjoblom" wrote in message
...
One way, assume the text strings/numbers are in column A starting in A1

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

Enter the formula with ctrl + shift & enter
Copy down

Regards,

Peo Sjoblom

"Cheryl B." wrote:

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

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


Peo

"Andy B" wrote:

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.

"Peo Sjoblom" wrote in message
...
One way, assume the text strings/numbers are in column A starting in A1

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

Enter the formula with ctrl + shift & enter
Copy down

Regards,

Peo Sjoblom

"Cheryl B." wrote:

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

Hi,

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

DataText to Columns
DelimitedNext
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,


"Cheryl B." wrote:

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

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 wrote:
One way, assume the text strings/numbers are in column A starting in A1

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

Enter the formula with ctrl + shift & enter
Copy down

Regards,

Peo Sjoblom

"Cheryl B." wrote:


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

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


--
Regards,

Peo Sjoblom


"Larry Stiff" wrote in message
...
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 wrote:
One way, assume the text strings/numbers are in column A starting in A1

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

Enter the formula with ctrl + shift & enter
Copy down

Regards,

Peo Sjoblom

"Cheryl B." wrote:


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