Reverse string search in Excel

Question

Trying to get Column F/VENDOR # to populate the vendor number only. The vendor number are highlighted. My strategy is from the right, find the third "_" and substitute it with a "|". Then anything right of the pipe is populated in column D.

However the ones with more than three "_" are not following the logic. What am I doing wrong?

Column D formula =IF(ISERROR(FIND("_",C2)),"",RIGHT(C2,LEN(C2)-FIND("|",SUBSTITUTE(C2,"_","|",LEN(C2)-LEN(SUBSTITUTE(C2,"_","",3))))))

Column F/Vendor# formula =IF(ISERROR(LEFT(D2,FIND("_",D2)-1)),"",LEFT(D2,FIND("_",D2)-1))

The problem is that the substitution is being made at the last "_" that is found. I need to specify to replace the third occurrence from right to left. Any help is appreciated. — DigitalSea
– DigitalSea, Commented Jun 17, 2017 at 4:02

Robin Mackenzie · Accepted Answer · 2017-06-17 04:14:22Z

The issue is in the column D formula - you have:

...LEN(C2)-LEN(SUBSTITUTE(C2,"_","",3))...

It should be:

...LEN(C2)-LEN(SUBSTITUTE(C2,"_",""))-2...

Giving a full formula for column D of:

=IF(ISERROR(FIND("_",A17)),"",RIGHT(A17,LEN(A17)-FIND("|",SUBSTITUTE(A17,"_","|",LEN(A17)-LEN(SUBSTITUTE(A17,"_",""))-2))))

The reason is because that part of the formula is really being used to calculate an index in another SUBSTITUTE function. You need to use a relative offset (-2 is kind of 3rd from right) if you have a unknown number of _s in the string.

If you can use VBA then you should look at using an UDF with regular expressions as I feel this is slightly less complex than the double-formula method which is not trivial to step through. The UDF could simply be this:

Option Explicit

Function GetVendorNumber(rng As Range) As String
    Dim objRegex As Object
    Dim objMatches As Object

    GetVendorNumber = ""
    Set objRegex = CreateObject("VBScript.RegExp")
    With objRegex
        .Pattern = "\D+_(\d+)_.+"
        Set objMatches = .Execute(rng.Text)
        If objMatches.Count = 1 Then
            GetVendorNumber = objMatches(0).SubMatches(0)
        End If
    End With

End Function

Collectives™ on Stack Overflow

Reverse string search in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related