CODEWITHSUNDEEP
excel
Gary's Student
Gary's Student

Reputation: 96753

Get Digits at the end of a String

The strings are mixed alphas and digits, but there is always a set of digits at the end of the string.

Any leading digits or digits in the middle of the string should be ignored. I came up with:

Public Function trailing(S As String) As Long
    Dim r As String
    Dim i As Long

    For i = Len(S) To 1 Step -1
        If IsNumeric(Mid(S, i, 1)) Then
            r = Mid(S, i, 1) & r
        Else
            Exit For
        End If
    Next i
    trailing = CLng(r)
End Function

It seems to work:

enter image description here

However the user is working on a .xlsx and can't use UDFs. Is there a formula that gets the same results ??
Thanks in advance

Upvotes: 2

Views: 217

Answers (3)

BrakNicku
BrakNicku

Reputation: 5991

Another possible solution:

=AGGREGATE(14,6,--RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)

In some cases it could give wrong results:

aaa12.1 bc1E+13

As a workaround you can use SUBSTITUTE to replace E, e and . (maybe also , and space) in input string with another letter.

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

In cell B1 and copied down:

=--RIGHT(A1,LOOKUP(2,1/(ISNUMBER(--RIGHT(A1,ROW($1:$15)))),ROW($1:$15)))

Simply make sure that the 15 in $1:$15 is going to be larger than the maximum possible number of ending digits for any given string. No array formula entry necessary, and no helper columns necessary.

Alternate version so you don't have to repeat ROW($1:$15):

=--RIGHT(A1,MATCH(TRUE,INDEX(ISERROR(--RIGHT(A1,ROW($1:$15))),),0)-1)

Upvotes: 5

JasonAizkalns
JasonAizkalns

Reputation: 20463

Alright, so bear with me...this is ugly and there could be some opportunity to clean this up (or a better approach altogether utilizing the same concepts) and since you already know that this is a much better candidate for RegEx/VBA, we are in agreement of a better approach, yet under the criteria/restrictions of your question, here we go...

Since you said the length varies between 2 and 12, we can make a set of array formulas in 12 columns, B-M which extracts the RIGHT() of 1, 2, ..., 12 characters accordingly. We wrap this with a VALUE to return an error #VALUE! for non-numerics -- NOTE: This formula is entered by first selecting cells B2:M2, typing the formula, =VALUE(RIGHT(A2, {1,2,3,4,5,6,7,8,9,10,11,12})) and then the obligatory CTRL + SHIFT + ENTER

We then find the position of the first error in COLUMN O with a MATCH() formula looking for the first TRUE of another array formula utilizing ISERROR().

We get the solution by doing an index of the position 1-12 columns returning the column next to the first error it finds. This is because you know that column A will always end in a number.

N.B. Since you specified that it is only a 4-digit numeric string at the end, you can probably get away with reducing this method to the first 4 columns; that is, rather than utilizing 12 columns, just use columns B:E and continue as above.

Excel

We end by begging the client/customer to consider a .xlsm workbook/VBA/RegEx or other solution...

Upvotes: 1

Related Questions