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

 

[XOR LX]

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)

I win on sheer character length (unless you've found a flaw in my last).

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)

Thanks. I'll take a look at these. Can't believe it's had to come to that...all for the sake of a couple of numbers at the start of a string!!

Regards

 

[billszysz]

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

decimal separator in my country is comma ( "," )....so in this case your formula fails.:wink:
And what about dates on the begining? 2013-01-01 or 01/01/13... every format of dates is not covered by your formula.
So... as i said previously... solution for case, not for every cases.

Regards

 

[XOR LX]

decimal separator in my country is comma ( "," )....so in this case your formula fails.:wink:
And what about dates on the begining? 2013-01-01 or 01/01/13... every format of dates is not covered by your formula.
So... as i said previously... solution for case, not for every cases.

Regards

We're not concerned about dates at the beginning here...numbers only, remember?

Fair point about the decimal separator, though!

Regards

 

[billszysz]

We're not concerned about dates at the beginning here...numbers only, remember?

Fair point about the decimal separator, though!

Regards

literally.... dot (or comma) is not a number

Regards

EDIT: Ok.... ommiting the separator..... try this string "12 000.10xxxxx"

 

[Rick Rothstein]

EDIT: Ok.... omitting the separator..... try this string "12 000.10xxxxx"

I assume you posted this example because the space is your thousands separator and I assume your implied question is "If the decimal point would be considered as part of the number, then why not the thousands separator?" Well, that is a good question... and a troubling one as well. It would be hard to program the acceptance of the thousands separator because the format for separating number depends on locale (0,000,000,000 in the US and lots of other places, but in India it is 0,00,00,000). Recognizing properly separated values would be somewhat problematic. For example, sticking with the comma as the thousands separator and assuming they are to be respected as part of the number, something like 1,23,456 would return 1,23,456 in India, but 1 in the US. I would guess this would be nearly impossible to work around in a formula and probably require a ton of code in a UDF (although I will think about this and see if I can work it into my UDFs.

decimal separator in my country is comma ( "," )....

literally.... dot (or comma) is not a number

Speaking about my UDFs, you raise an excellent point about the decimal point not always being a dot. My previously posted UDFs only considered the decimal point to be a dot, the modified versions below figure out what the decimal point is for the computer running the code and uses that...

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

 

[XOR LX]

decimal separator in my country is comma ( "," )....so in this case your formula fails.:wink:
And what about dates on the begining? 2013-01-01 or 01/01/13... every format of dates is not covered by your formula.
So... as i said previously... solution for case, not for every cases.

Regards

Perhaps (array-entered):

=0+LEFT(A1,MATCH(FALSE,ISNUMBER(MATCH(CODE(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)),1)),44+{0,2,4,5,6,7,8,9,10,11,12,13},0)),0)-1)

Regards