Macro help to trim field

[TimmerSuds]

I have a column that I am looking to create a macro for to trim the leading zeros on all cells that have a value in them.

The first cell is J2 (J1 is header text) and I need it to trim all cells in the column beginning with J2 and including all rows that have a value.

The cells have values like:
00145723
00451283
00051876

I need it to trim the leading zeros.

I appreciate any help.

Thanks!

[jindon]

VBA?

1) Select Col.J
2) go to [Data] -[Text To Columns] - hit [OK]

HTH

[Pepe Le Mokko]

@timmersuds
Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[Pepe Le Mokko]

@timmersuds

Administrative Note:

Hi and welcome
although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
So, please update your profile to something more precise then "Earth" ( country will suffice, no need to be more precise).
Thank you for helping us to help you

[Rick Rothstein]

1) Select Col.J
2) go to [Data] -[Text To Columns] - hit [OK]

One caution with jindon's suggestion... if your header text contains spaces, commas or semi-colons in it, then select Delimited and click Next when the Text To Columns dialog appears instead of clicking Finish (jindon accidentally called the button's caption as OK)... then when the next dialog page the appears, uncheck any of the characters that are in your header text... then click the Finish button. The reason for this caution is because the Text To Columns dialog box "remembers" its setting from call to call whether set manually in the dialog box or set via a previous call from VBA code so you can never be sure what the settings are when you go to use it.

[nigelog]

one vba way

Please Login or Register  to view this content.

[TimmerSuds]

Thanks for the help - both suggestions work great!

[jindon]

You are welcome and thanks for the rep.