CODEWITHSUNDEEP
vbaexcelexcel-formula
eccentricCoder
eccentricCoder

Reputation: 844

Extracting substring based on different criterias and placing the extracted string in another cell of same row

I have an excel file, in which there is a column containing a specific string. This string doesn't follow any particular pattern. My requirement is to extract a sub-string (product id) which is a set of 8 consecutive numbers that have to be preceded/followed by any no of characters or must be at the start or end of the string.

Following are some examples.

Scenario 1:

product id is preceded by #

Id#53298632/BS TVR:003519

Function used in excel

=MID(N88,FIND("#",N88)+1,8)

* result : 53298632 *

Scenario 2:

product id is at the beginning

53298632:003519

Function used in excel

=MID(A1,1,8)

* result : 53298632 *

At the beginning I had to deal with only scenario 1 and hence used the specified formula. Now a days the string doesnt follow any particular pattern but my product id still comes as 8 digit consecutive numbers. I searched for a suitable solution and found this formula (which I dont clearly understand).

=LOOKUP(10^8,MID(N132,ROW(INDIRECT("1:"&LEN(N132)-7)),8)+0)

This does work in most of the cases but in some cases it fails

For example

Pdr#53298632/ QTY NOS 1031949

Here the result is 1031949 which is definitely not what I want. The result should have been 53298632

Please help me fix this. Can this be done using VBA macro? I am completely new to such excel functions VBA and macro.

Any help will be highly appreciated!

Thanks in advance.

Upvotes: 0

Views: 87

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34285

I guess you could also modify the original formula to pick up the first match of an 8-digit number

=MID(A1,MATCH(TRUE,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)+0),0),8)

(must be entered as an array formula using CtrlShiftEnter).

Upvotes: 1

VirtualMichael
VirtualMichael

Reputation: 721

If you are happy to specifically include the Microsoft RegEx module into your Excel project, regular expressions will solve this reasonably quickly.

To add the RegEx function to use in your Excel Macros, select the Developer menu in Excel and start the Visual Basic editor. Within the VBA for Applications window, Select Tools->References and select Microsoft VBScript Regular Expressions 5.5.

Create a new Module for your VBAProject (right-click on your Excel file name in the project tree and click Insert->Module)

Double click on the newly created Module (within the project tree) and enter the following code in the Module1 (Code) window:

Public Function getProductCode(source As String) As String
    Dim strPattern As String: strPattern = "(\d{8})"
    Dim result As String: result = ""
    Dim results As Object
    Dim regEx As New RegExp
    With regEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = False
        .Pattern = strPattern
    End With
    If regEx.Test(source) Then
        Set results = regEx.Execute(source)
        If (results.Count <> 0) Then
           result = results.Item(0)
        End If
    End If
    getProductCode = result
End Function

From the relevant cell in Excel, you can now call the macro:

=getProductCode(A1)

Upvotes: 1

Related Questions