Avatar of John Carney
John Carney
Flag for United States of America asked on

Parsing an alphanumeric string to extract individual implied elements from free text

I have two functions ('GetSeats' and 'GetSeats2') that look at free text and extract certain strings from it. Please take a look at the attached workbook and let me know how to modify the two functions so that everything is parsed down to the individual seats. Something perhaps that mimics the logic of the formula in AF25?

I'd try to tell you more here but I think the workbook will speak for itself better.


Microsoft ExcelRegular Expressions

Avatar of undefined
Last Comment
John Carney

8/22/2022 - Mon
Terry Woods

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Terry Woods

(v_letter being an individual letter)
John Carney

Thanks Terry.

RE: "the regular expression pattern (with ignore case turned on): (\d+)([a-z]+)"

As simply as possible, what would be the full code that would extract the alphanumeric combos, including the part that specifies where the extracted strings will go?

Just that part of the answer - assuming it can be isolated - would be very helpful.


John Carney

I just realized that I have a macro (courtesy of byronwall) that does exactly what I want and puts each individual set in its own cell, text-to-columns style. Looking at the code below, is there a way to take its logic and incorporate it into the two functions so that the separate iterations produced by the macro go into the cells in Column AD (comma-separated) automatically?

You can view the orihginal question here if that's of any help: https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26708787.html

Sub GetSeatDetails()
    Dim block As Range
    Dim cell As Range
    Dim parts As Variant
    Dim chunk As Variant
    Dim cleans As Variant
    Dim number_loc As Integer
    Dim LtrNum As Integer
    Set block = [AllSeatsAffected] '.SpecialCells(xlCellTypeConstants)
On Error Resume Next
    For Each cell In [AllSeatsAffected]
        LtrNum = 0
        parts = Split(cell, ",")    'split text into chunks
            For Each cleans In parts    'iterate parts
                chunk = Replace(cleans, " ", "")   'check if numeric
                number_loc = 3   'check for number that is 3,2, or 1 digits long
                    If Not IsNumeric(Left(chunk, 3)) Then
                                number_loc = 2
                                If Not IsNumeric(Left(chunk, 2)) Then
                                    number_loc = 1
                                    If Not IsNumeric(Left(chunk, 1)) Then
                                        number_loc = 0
                                    End If
                                End If
                    End If
                If number_loc > 0 Then      'make sure that a number was found
                    Letters = Right(chunk, Len(chunk) - number_loc)     'grab the letters
                    If Len(Letters) > 0 Then        'make sure we have multiple letters
                        For i = 1 To Len(Letters)
                            LtrNum = LtrNum + 1       'add the new string to the next column
                            cell.Offset(, LtrNum + [Gap].Value) = Left(chunk, number_loc) & Mid(Letters, i, 1)
                        Next i
                    End If
                End If
            Next cleans
    Next cell
    'Call GetIndivSeats
End Sub

Open in new window

Your help has saved me hundreds of hours of internet surfing.
John Carney

By "each individual set" I mean "each individual seat."
John Carney

Hi Terry, I couldn't figure out how to make this work yet but you I might be able to eventually, and you have been so helpful on this general issue in the past that I want to award you the points.