Probably a Stupid Question

[Paige]

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?

 

[Bernie Deitrick]

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

 

[T. Valko]

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)

 

[Jim Thomlinson]

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.

 

[Bernie Deitrick]

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

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


HTH,
Bernie
MS Excel MVP

 

[Jim Thomlinson]

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

 

[T. Valko]

Thanks!

It's both an array and volatile but, as Bernie mentioned in his reply, after
you get everything processed you can always Copy>Paste Special>Values to
convert to constants.

 

[Paige]

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]

You're welcome. Thanks for the feedback!

 

[Grandfeller]

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]

Grandfeller,

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

Bernie
MS Excel MVP