Formula Extract Characters at the Beginning of a String

[gonesalsa]

Hello
I need to extract the numbers at the beginning of a string. The problem is that there could 1, 2 or 3 digits. Here are examples of the string:

4JXXXXXXXXX1204
10EXXXXXXXXX1204
127JXXXXXXXXX1204

I only want to extract the 4, 10 and the 127 in the above strings. I tried =LEFT(B1,LEN(B1)-14), but the formula isn't working on the last string where the number is 3 digits. it only pulls 12, instead of 127.

Thanks

 

[Rick Rothstein]

All good points.

And, of course, that raises a similar point for the leading number formula as well. The array-entered formula I posted originally, namely this one...

=LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)),1)),0)-1)

returns 12 from a text string like 12.34ABC whereas this variant (equivalent to what I did originally with the trailing number formula)...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

returns 12.34 and, again, the correct one to use depends on what user requires from the data.

 

[XOR LX]

And, of course, that raises a similar point for the leading number formula as well. The array-entered formula I posted originally, namely this one...

=LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)),1)),0)-1)

returns 12 from a text string like 12.34ABC whereas this variant (equivalent to what I did originally with the trailing number formula)...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

returns 12.34 and, again, the correct one to use depends on what user requires from the data.

I guess you have to get a touch more advanced if you want a "universal" solution (i.e. which works for decimals as well), e.g. by either calling on the CODE function:

=LEFT(A1,MATCH(FALSE,ABS(51.5-CODE(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)),1)))<6,0)-1)

or another explicit MATCH:

=LEFT(A1,MATCH(FALSE,ISNUMBER(MATCH(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)),1),""&{0,1,2,3,4,5,6,7,8,9,"."},0)),0)-1)

Regards

 

[Rick Rothstein]

I guess you have to get a touch more advanced if you want a "universal" solution (i.e. which works for decimals as well), e.g. by either calling on the CODE function:

=LEFT(A1,MATCH(FALSE,ABS(51.5-CODE(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)),1)))<6,0)-1)

or another explicit MATCH:

=LEFT(A1,MATCH(FALSE,ISNUMBER(MATCH(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)),1),""&{0,1,2,3,4,5,6,7,8,9,"."},0)),0)-1)

My second array-entered formula already does this...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

The only point I was raising is if the formula should recognize the dot as a decimal point or not.

By the way, I notice a small flaw in my formula as well as your two latest ones... if you have a value like "123.ABC" then the returned value is "12." with the attached dot.

 

[XOR LX]

My second array-entered formula already does this...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

There's always something...! This will fail with spaces, e.g. 12 ABC

The only point I was raising is if the formula should recognize the dot as a decimal point or not.

By the way, I notice a small flaw in my formula as well as your two latest ones... if you have a value like "123.ABC" then the returned value is "12." with the attached dot.

Yes. That is a problem. Of course, my CODE solution would also fail on 12/ABC, so you'd first have to substitute out "/"...not ideal.

Back to the drawing-board...

 

[Rick Rothstein]

My second array-entered formula already does this...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

There's always something...! This will fail with spaces, e.g. 12 ABC

Of course, my CODE solution would also fail on 12/ABC, so you'd first have to substitute out "/"...not ideal.

Back to the drawing-board...

Is there no end to this!!! I am beginning to think the only good way to do this is with a well-crafted UDF.

 

[XOR LX]

Hahaha!! Perserverance!!

 

[Rick Rothstein]

My second array-entered formula already does this...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

There's always something...! This will fail with spaces, e.g. 12 ABC

Is there no end to this!!! I am beginning to think the only good way to do this is with a well-crafted UDF.

Incredible! As it turns out, the formula also fails if the value in the cell is just a number by itself (no other text).

I believe the following array-entered formula corrects both problems and still retains the original functionality (but who knows, I'm just waiting apprehensively for the next "it doesn't work with..." to come along

)...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(SUBSTITUTE(A1," ","X")&"X",ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

**Commit his formula using CTRL+SHIFT+ENTER and not just Enter by itself

Hahaha!! Perserverance!!

That is starting to get harder and harder to do!

 

[billszysz]

=IFERROR(LEFT(A1,MATCH(FALSE,ISNUMBER(-RIGHT(LEFT(A1,ROW($1:$10)))),0)-1),LEFT(A1,10))
and
=IFERROR(RIGHT(A1,MATCH(FALSE,ISNUMBER(-LEFT(RIGHT(A1,ROW($1:$10)))),0)-1),RIGHT(A1,10))
This is the text so leading zeros are preserved
Of course we can change the result to numbers.

 

[XOR LX]

Incredible! As it turns out, the formula also fails if the value in the cell is just a number by itself (no other text).

I believe the following array-entered formula corrects both problems and still retains the original functionality (but who knows, I'm just waiting apprehensively for the next "it doesn't work with..." to come along )...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(SUBSTITUTE(A1," ","X")&"X",ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

I'm afraid you were right. This still fails on e.g. 123.

=IFERROR(LEFT(A1,MATCH(FALSE,ISNUMBER(-RIGHT(LEFT(A1,ROW($1:$10)))),0)-1),LEFT(A1,10))

This doesn't appear to take account of decimals, e.g. 123.45ABC

Here's my modification on an earlier attempt, which I think (!!) covers everything:

=0+LEFT(A1,MATCH(FALSE,ABS(51.5-CODE(MID(SUBSTITUTE(A1&"|","/","|"),ROW(INDEX(A:A,1):INDEX(A:A,99)),1)))<6,0)-1)

Regards

 

[Rick Rothstein]

I'm afraid you were right. This still fails on e.g. 123.

Sigh! I just knew it was too good to be true. How about this array-entered formula then...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(SUBSTITUTE(IF(ISNUMBER(-MID(A1,FIND(".",A1&".")+1,1)),A1,SUBSTITUTE(A1,".","X"))," ","X")&"X",ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

By that way, I was serious about trying to come up with UDFs instead of trying to keep bashing Excel "along side of it head" trying to get it to make its functions "behave". This is my initial attempt (initial because I'm sure there must be something I missed)...

Function LeadingNumber(ByVal S As String, _
                       Optional WithDecimalPoint As Boolean, _
                       Optional ShowTrailingDot As Boolean) As String
  Dim X As Long
  If Len(S) Then
    S = S & "X"
    For X = 1 To Len(S)
      If Mid(S, X, 1) Like "[!0-9" & Left(".", -WithDecimalPoint) & "]" Then
        S = Left(S, X - 1)
        If Not ShowTrailingDot And Right(S, 1) = "." Then S = Left(S, Len(S) - 1)
        Exit For
      End If
    Next
  Else
    S = ""
  End If
  LeadingNumber = S
End Function

Function TrailingNumber(ByVal S As String, _
                        Optional WithDecimalPoint As Boolean, _
                        Optional ShowTrailingDot As Boolean) As String
  Dim X As Long
  If Len(S) Then
    S = "X" & S
    For X = Len(S) To 1 Step -1
      If Mid(S, X, 1) Like "[!0-9" & Left(".", -WithDecimalPoint) & "]" Then
        S = Mid(S, X + 1)
        If Not ShowTrailingDot And Right(S, 1) = "." Then S = Left(S, Len(S) - 1)
        Exit For
      End If
    Next
  Else
    S = ""
  End If
  TrailingNumber = S
End Function

Both functions allow you to specify whether decimal points should be counted as part of the number or as a non-number break point via the optional WithDecimalPoint argument (defaulted to False... the dot is a non-number break point). Both functions also allow you to show a trailing dot (when there is one... 12. with nothing following it for example) or not via the optional ShowTrailingDot argument (defaulted to False... trailing dots will not be shown).