CODEWITHSUNDEEP
regexexcelexcel-formula
DigitalSea
DigitalSea

Reputation: 191

Reverse string search in Excel

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))

enter image description here

enter image description here

Upvotes: 2

Views: 1253

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

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

Upvotes: 1

Related Questions