Normalize Numbers

Can some one please assist. I have a column of alpha numeric numbers with leading zeros which i need to normalize. (remove leading zeros).

They have been copied from another spread sheet where they are formatted as General.

Can a macro do this?
Example Need to Convert to
Column F
HOUSEAWB
02269MTA 2269MTA
1524GLC 1524GLC
0078TCL 78TCL
0100AZO 100AZO
0102AZ 102AZO
0111TMB 11TBM


THANK YOU

a couple of functions in another column can do it
either if format is
00123abc or 0123def ect i.e there are always numbers before letters then
=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),99
will do it
however if you may have 000abc123
=IF(MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))<=LEN(A1),MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),99),SUBSTITUTE(A1,"0",""))

I would just turn on the macro recorder and let it record you entering that formula into an adjacent cell, then copy and EDIT > PASTESPECIAL > VALUE back over the original cell. Then turn off the recorder.

Now you have a VBA version of how to enter that .FormulaR1C1 = into your original code.

As you move that code into your macro, apply the formula to the ENTIRE range. I haven't test the actual formula much, but once you've done as I've suggested above, you get something like this:

Please Login or Register  to view this content.

As you can see, I'm entering the formulas into column M. You can change that to any empty column out to the right.

TRY

Please Login or Register  to view this content.

Originally Posted by martindwilson

TRY

Please Login or Register  to view this content.

Short and Sweet! Very nice...