Link to home
Create AccountLog in
Avatar of John Carney
John CarneyFlag 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.


Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
(v_letter being an individual letter)
Avatar of 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.


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:

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

By "each individual set" I mean "each individual seat."
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.