Probably a Stupid Question
 

I have data formatted as text, with leading zeroes, such as 00AF234, 014F,
etc. Is there a way to remove these leading zeroes (other than VB)? The
data could be of any length and have 0, 1, 2 or more leading zeroes. Can't
do find/replace because other zeroes (like in the middle of the data) would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

Paige,

If you data starts in cell A1, use this formula in B2:
=IF(LEFT(A2,1)="0",MID(A2,2,LEN(A2)),A2)
and copy to the left for as many columns as you might have leading zeros (it takes 4 columns of
formulas to remove 4 leading zero characters), so you may have to insert extra columns.

Then copy the last column of results and paste as values over the original data, and then remove the
columns of formulas.


Or select all the cells, and run this macro:

Sub RemoveLeadingZero()
Dim myC As Range
For Each myC In Selection
While Left(myC.Value, 1) = "0"
myC.Value = Mid(myC.Value, 2)
Wend
Next myC
End Sub


HTH,
Bernie
MS Excel MVP


"Paige" wrote in message
...
I have data formatted as text, with leading zeroes, such as 00AF234, 014F,
etc. Is there a way to remove these leading zeroes (other than VB)? The
data could be of any length and have 0, 1, 2 or more leading zeroes. Can't
do find/replace because other zeroes (like in the middle of the data) would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

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)

--
Biff
Microsoft Excel MVP


"Paige" wrote in message
...
I have data formatted as text, with leading zeroes, such as 00AF234, 014F,
etc. Is there a way to remove these leading zeroes (other than VB)? The
data could be of any length and have 0, 1, 2 or more leading zeroes.
Can't
do find/replace because other zeroes (like in the middle of the data)
would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

Here is a possible solution. A bit ugly but it will work... Add a helper
column to the source data. In this column concatenate a character that you do
not find in your text field to the front of your text string. Assuming your
text field is in column A then use a formula such as
="-" & A1
Copy that formula down to concatenate the the dash to the front of the text
field. Now copy and paste values so that you end up with text that looks like
this
-00AF234
-014F

Now you can do a find and replace looking for -00 and replacing with
nothing. Then do -0 replacing with nothing...

Like I said a bit ugly but it will work.
--
HTH...

Jim Thomlinson


"Paige" wrote:

I have data formatted as text, with leading zeroes, such as 00AF234, 014F,
etc. Is there a way to remove these leading zeroes (other than VB)? The
data could be of any length and have 0, 1, 2 or more leading zeroes. Can't
do find/replace because other zeroes (like in the middle of the data) would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

If you data starts in cell A1, use this formula in B2:

Sorry - I should have said cell A2....


HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Paige,

If you data starts in cell A1, use this formula in B2:
=IF(LEFT(A2,1)="0",MID(A2,2,LEN(A2)),A2)
and copy to the left for as many columns as you might have leading zeros (it takes 4 columns of
formulas to remove 4 leading zero characters), so you may have to insert extra columns.

Then copy the last column of results and paste as values over the original data, and then remove
the columns of formulas.


Or select all the cells, and run this macro:

Sub RemoveLeadingZero()
Dim myC As Range
For Each myC In Selection
While Left(myC.Value, 1) = "0"
myC.Value = Mid(myC.Value, 2)
Wend
Next myC
End Sub


HTH,
Bernie
MS Excel MVP


"Paige" wrote in message
...
I have data formatted as text, with leading zeroes, such as 00AF234, 014F,
etc. Is there a way to remove these leading zeroes (other than VB)? The
data could be of any length and have 0, 1, 2 or more leading zeroes. Can't
do find/replace because other zeroes (like in the middle of the data) would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

Impressive. This is a much better solution than mine...
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Paige" wrote in message
...
I have data formatted as text, with leading zeroes, such as 00AF234, 014F,
etc. Is there a way to remove these leading zeroes (other than VB)? The
data could be of any length and have 0, 1, 2 or more leading zeroes.
Can't
do find/replace because other zeroes (like in the middle of the data)
would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

Thanks!

It's both an array and volatile but, as Bernie mentioned in his reply, after
you get everything processed you can always CopyPaste SpecialValues to
convert to constants.

--
Biff
Microsoft Excel MVP


"Jim Thomlinson" wrote in message
...
Impressive. This is a much better solution than mine...
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Paige" wrote in message
...
I have data formatted as text, with leading zeroes, such as 00AF234,
014F,
etc. Is there a way to remove these leading zeroes (other than VB)?
The
data could be of any length and have 0, 1, 2 or more leading zeroes.
Can't
do find/replace because other zeroes (like in the middle of the data)
would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

WOW!!!!! You are all awesome! Thanks for responding so quickly. Am going
with the =MID formula.....is the easiest, but keeping the other 2 for
reference also cause you never when they might come in handy. I'm just
constantly amazed at the brain power out there! Have a good weekend....

"T. Valko" wrote:

Thanks!

It's both an array and volatile but, as Bernie mentioned in his reply, after
you get everything processed you can always CopyPaste SpecialValues to
convert to constants.

--
Biff
Microsoft Excel MVP


"Jim Thomlinson" wrote in message
...
Impressive. This is a much better solution than mine...
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Paige" wrote in message
...
I have data formatted as text, with leading zeroes, such as 00AF234,
014F,
etc. Is there a way to remove these leading zeroes (other than VB)?
The
data could be of any length and have 0, 1, 2 or more leading zeroes.
Can't
do find/replace because other zeroes (like in the middle of the data)
would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Paige" wrote in message
...
WOW!!!!! You are all awesome! Thanks for responding so quickly. Am going
with the =MID formula.....is the easiest, but keeping the other 2 for
reference also cause you never when they might come in handy. I'm just
constantly amazed at the brain power out there! Have a good weekend....

"T. Valko" wrote:

Thanks!

It's both an array and volatile but, as Bernie mentioned in his reply,
after
you get everything processed you can always CopyPaste SpecialValues to
convert to constants.

--
Biff
Microsoft Excel MVP


"Jim Thomlinson" wrote in
message
...
Impressive. This is a much better solution than mine...
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Paige" wrote in message
...
I have data formatted as text, with leading zeroes, such as 00AF234,
014F,
etc. Is there a way to remove these leading zeroes (other than VB)?
The
data could be of any length and have 0, 1, 2 or more leading zeroes.
Can't
do find/replace because other zeroes (like in the middle of the
data)
would
also be removed. Nothing I've tried seems to work, short of sorting
by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

Bernie,

Not only does your macro make this task easy (I had the same problem this
thread addresses), but convinced me to used VB macros more. Thanks.

"Bernie Deitrick" wrote:

Paige,

If you data starts in cell A1, use this formula in B2:
=IF(LEFT(A2,1)="0",MID(A2,2,LEN(A2)),A2)
and copy to the left for as many columns as you might have leading zeros (it takes 4 columns of
formulas to remove 4 leading zero characters), so you may have to insert extra columns.

Then copy the last column of results and paste as values over the original data, and then remove the
columns of formulas.


Or select all the cells, and run this macro:

Sub RemoveLeadingZero()
Dim myC As Range
For Each myC In Selection
While Left(myC.Value, 1) = "0"
myC.Value = Mid(myC.Value, 2)
Wend
Next myC
End Sub


HTH,
Bernie
MS Excel MVP


"Paige" wrote in message
...
I have data formatted as text, with leading zeroes, such as 00AF234, 014F,
etc. Is there a way to remove these leading zeroes (other than VB)? The
data could be of any length and have 0, 1, 2 or more leading zeroes. Can't
do find/replace because other zeroes (like in the middle of the data) would
also be removed. Nothing I've tried seems to work, short of sorting by
length, then doing =right. Any ideas please?

Probably a Stupid Question
 

Grandfeller,

Glad to hear it - more macros is a good thing ;-)

Bernie
MS Excel MVP


Bernie,

Not only does your macro make this task easy (I had the same problem this
thread addresses), but convinced me to used VB macros more. Thanks.