Best way to remove leading zeros from a non-numeric value in Excel

Best way to remove leading zeros from a non-numeric value in Excel I have many cells in an Excel sheet, using 9 characters of 0-9 and A-Z, that have some number of prefixed zeros:
Asked by: Guest \| Views: 270

Total answers/comments: 2

Comments display order:

Guest [Entry]

"Here's a solution that's cell-intensive but correct.

Put your data in column A.

In B1, put the formula:

=IF(
LEFT(A1) = ""0"" ,
RIGHT(A1, LEN(A1)-1),
A1)

This checks for a single leading zero and strips it out.

Copy this formula to the right as many columns as there can be characters in your data (9, in this case, so you'll be going out to column J). Copy it down for each row of data.

The last column contains your data, stripped of leading zeros."

Guest [Entry]

"This is a tough problem to do with a worksheet function.
The following will do the trick, but only if the zeros that aren't leading come only one at a time, and there are no trailing zeroes, and no embedded spaces.

It substitutes all zeros with spaces, trims them (all but embedded single spaces), then substitutes back the zeroes.

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,""0"","" "")),"" "",""0"")"