CODEWITHSUNDEEP
excelexcel-formula
RaBan
RaBan

Reputation: 1

How to eliminate leading zeroes in a cell that begins with text and has text and numbers

In an excel column I have a cell that contains MATH 0128 and another that contains MATH 0015. I want a formula or function that will change the cell from MATH 0128 to MATH 128 and MATH 0015 to MATH 15.

I found 

=MID(A2,MATCH(TRUE,(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)<>"0"),0),LEN(A2))

for cell A2, but that only seems to work if the cell begins with 0, say 000Math0128 to Math 0128.

All my cells begins with text.

Upvotes: 0

Views: 52

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

Assuming the common feature of these strings is the space between the leading word and the numeric part, and that there are no other characters after the numeric part:

=LEFT(A2,FIND(" ",A2))&VALUE(MID(A2,FIND(" ",A2)+1,9999))

Alternatively, and more general, for versions of Excel that support FilterXML

=TEXTJOIN(" ",TRUE,FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b"))

This will process a string of space separated AlphaNumeric and Numeric components, removing leading 0's from numeric components. (you can replace the space separator with any other separator of your choosing)

Some examples

enter image description here

Upvotes: 1

Mech
Mech

Reputation: 4015

Based on your example, this is what you need:

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

You have other options, like looping in VBA (using REPLACE instead of SUBSTITUTE).

If you wanted to add more options to the above formula, you would just wrap it in more SUBSTITUTEs. 

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

Upvotes: 0

Related Questions