ExcelBanter - how do I eliminate leading "0s" in an alpha numeric cell

On Wed, 13 Sep 2006 15:39:02 -0700, REST
wrote:

I have ID numbers that are alpha numeric. Some are all numeric formatted as
text, others are actual alpha numeric combinations. Most all have leading
"0s". I was hoping I would be able to use a function in a formula to easily
eliminate them.

Give some examples of the values.

If the values only include numbers, you can remove the leading zeros with a
formula:

=--A1

That will convert the value to a number and the appearance will depend on the
formatting of the cell. If the format is General, no leading zeros will
appear.

If the value might contain both numbers and letters, and you want to strip off
the leading zeros, then one way would be to use this **array** formula:

=MID(A1,MATCH(FALSE,"0"=MID(A1,ROW(INDIRECT("1:"&L EN(A1))),1),0),255)

To enter an array formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

OR

you could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ and use the Regular Expression formula:

=REGEX.MID(A1,"[^0].*")

--ron