How can I parse a string from a cell multiple time in excel?

debugcn Published at Dev

Centimane

I have a column in my datasheet that often contains a string that I want to place in another column. This string may occur multiple times and I want to place ALL instances of it into the other column. The column is delimited and I'd like to take the matching string and up to the delimiter.

An example:

Possessions
Fruit: apple, Car: Ford, Fruit: banana,
Car: Saturn,
Fruit: orange,

I'd like the next column to contain:

Fruit
Fruit: apple, Fruit: banana,

Fruit: orange,

It's easy enough to find the first instance of the string (new lines are for readability):

MID(A2, 
    FIND( *first instance of Fruit:* ), 
    FIND( *first comma after Fruit:* ) - FIND( *first instance of Fruit:* )
    )

However I could encounter the string any number of times and want to catch all of them.

Also, the column is already a calculated field (a reference to another sheet) so I cannot use text to columns to split on the delimiter.

Any ideas on how to return all instances of the string? I'd rather avoid a VBA script if possible and use worksheet functions, but if it's not possible with functions I'm open to VBA.

nixda

Generic VBA solution with regular expressions

This method should cover your needs. It can also be used by other users to extract multiple strings from a given string with the help of regular expressions

Open your VBA editor (ALT+F11)
Insert a new module (!) and paste the below code into it
Go back to Excel and use this formula in a cell where you want your output
```
=REGEXTRACT(A1, "Fruit: .*?,")
```

Formula explanation

=REGEXTRACT() is your new custom formula.
A1 is the cell where your input data is placed
Fruit: .*?, is a regular expression to find all occurrence of fruit and matches until the very next comma.

Function REGEXTRACT(objCell As Range, strPattern As String)

    Dim objMatches As Object
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")

    RegEx.IgnoreCase = True
    RegEx.Global = True
    RegEx.Pattern = strPattern

    Set objMatches = RegEx.Execute(objCell.Value)

    If objMatches.Count <> 0 Then
        For Each objMatch In objMatches
            REGEXTRACT= REGEXTRACT+ objMatch.Value
        Next objMatch
    Else: REGEXTRACT= ""
    End If

End Function

Hint: Look-Behind and Look-Ahead expressions are not supported under VB's regex engine. So it's not trivial to exclude the comma via RegEx. But it's possible via normal VBA string operations.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-21

Comments

0 comments

From Dev

Related Related

Article