Get Digits at the end of a String

Question

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:

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

Perhaps this can help? http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Peter B, Mar 25 '16 at 15:19
@PeterB Thanks for the reference..................but I can't use *VBA* in this case. — Gary's Student, Mar 25 '16 at 15:21
There's also a reference to an Excel plugin: http://seotoolsforexcel.com/regexpfind/ — Peter B, Mar 25 '16 at 15:23
What is the max length of the alpha-numerics in column A? If they are short enough, you can hack together a solution with some array formulas, but it won't be pretty — JasonAizkalns, Mar 25 '16 at 15:44
@JasonAizkalns There are 1 to 4 digits at the end of the string they will vary from **0** to **9999** — Gary's Student, Mar 25 '16 at 15:47
No, the actual `=len(A1)`, `=len(A2)`, etc... what's the max length? In the example, they are both 9, is it fair to say the length is a relatively small number? — JasonAizkalns, Mar 25 '16 at 15:48
This is a perfect candidate for Regex. Too bad it requires VBA. `\d+(\.\d+)?$` — radiocontrolled, Mar 25 '16 at 15:49
@JasonAizkalns The total length of the strings (including numeric suffices) varies between 12 and 2. — Gary's Student, Mar 25 '16 at 15:52

score 5 · Accepted Answer · answered Mar 25 '16 at 16:16

5

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)

answered Mar 25 '16 at 16:16

tigeravatar

26,199
5
30
38

What does the `--` mean? – findwindow Mar 25 '16 at 16:22
1

It means "Minus Negative *expression*". Basically, it converts text to a number if it is possible to do so. You could get the same result with `0+` or `1*`. I just like how `--` looks. – tigeravatar Mar 25 '16 at 16:24
Wow this is really clever! – findwindow Mar 25 '16 at 16:45

JasonAizkalns · Answer 2 · 2016-03-25T16:10:36.003

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.

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

score 1 · Answer 3 · answered Mar 25 '16 at 16:53

1

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.

answered Mar 25 '16 at 16:53

BrakNicku

5,935
3
24
38

Thanks....................I will check if my user's version of Excel will support *Aggregate()* – Gary's Student Mar 25 '16 at 16:57

Get Digits at the end of a String

3 Answers3