CODEWITHSUNDEEP
excelexcel-formula
hoggar
hoggar

Reputation: 3827

How to extract numbers with leading zeros from mixed Excel cells?

I need extract numbers with leading zeros from mixed cells?

Example cell A1 content:

A1: 0012 SomeText

I need in cell B1 only numbers with leading zeros:

B1: 0012

If I put formula =TEXT(LEFT(A1;FIND(" ";A1));"@") in cell formatted as General I get only numbers without leading zeros 12.

If I put formula in cell formatted as Text formula doesn't work, it shows formula text.

Upvotes: 1

Views: 1789

Answers (2)

VBasic2008
VBasic2008

Reputation: 55048

OP's Approach

=TEXT(LEFT(A1;FIND(" ";A1));REPT("0";FIND(" ";A1)-2)&"#")

Comma Version

=TEXT(LEFT(A1,FIND(" ",A1)),REPT("0",FIND(" ",A1)-2)&"#")

The previous versions raise the question where the trailing space got lost.

VBA Version

Function LZERO(ZeroString As String) As String
    LZERO = Split(ZeroString)(0)
End Function

e.g. =LZERO(A1)

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23285

Assuming your text is ####[space]xxxx, you can pull the numbers with LEFT() and SEARCH():

=TRIM(LEFT(A1;SEARCH(" ";A1)-1))

Upvotes: 4

Related Questions