Is there a VBA code using REGEX to help me delimit the 2 rightmost "*" under certain conditions?

[EmilyCrandall]

Emilia*Grace Johnson*IT0037201X*23
Megan*2 Richards*PE018187
Isabel Sneider*TEC017106S*43
Isaiah Lee*TEC017102X
Billy*Bob*3 Joel*TBF016052H*35


Is there a VBA Code using REGEX to delimit the employee ID and age at the end of the line using the "*"?

Some of my rows have up to 4 "*" (used for hyphenated names and/or the amount of positions worked) but I only want the employee ID and age to be delimited. Some rows don't have an age either.

(If it helps, the ages will always only be 2 digits)

Any ideas would help greatly.

 

[Eric W]

Welcome to the MrExcel forum!

I don't know if you have some particular reason for using REGEX, but there are many ways to accomplish that. For example:

Sub Test2()

    d = Range("A2:A6").Value
    ReDim res(1 To UBound(d), 1 To 2)
    
    For i = 1 To UBound(d)
        w = Split(d(i, 1), "*")
        u = UBound(w)
        If Len(w(u)) = 2 Then
            res(i, 1) = w(u - 1)
            res(i, 2) = w(u)
        Else
            res(i, 1) = w(u)
        End If
    Next i
    
    Range("C2:D6") = res
            
End Sub

This takes the values in A2:A6 and returns the values in C:D.

Book1
	A	B	C	D
1			ID	Age
2	Emilia*Grace Johnson*IT0037201X*23		IT0037201X	23
3	Megan*2 Richards*PE018187		PE018187
4	Isabel Sneider*TEC017106S*43		TEC017106S	43
5	Isaiah Lee*TEC017102X		TEC017102X
6	Billy*Bob*3 Joel*TBF016052H*35		TBF016052H	35
Sheet8

 

[Fluff]

Another option with a formula

++Fluff.xlsm
	A	B	C
1
2	Emilia*Grace Johnson*IT0037201X*23	IT0037201X	23
3	Megan*2 Richards*PE018187	PE018187
4	Isabel Sneider*TEC017106S*43	TEC017106S	43
5	Isaiah Lee*TEC017102X	TEC017102X
6	Billy*Bob*3 Joel*TBF016052H*35	TBF016052H	35
Main

Cell Formulas
Range	Formula
B2:C2,B6:C6,B4:C4,B3,B5	B2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(A2,"*","</m><m>")&"</m></k>","//m[position()>=last()-1]"),IF(COUNT(f),TRANSPOSE(f),INDEX(f,2)))
Dynamic array formulas.

 

[EmilyCrandall]

Another option with a formula

++Fluff.xlsm
	A	B	C
1
2	Emilia*Grace Johnson*IT0037201X*23	IT0037201X	23
3	Megan*2 Richards*PE018187	PE018187
4	Isabel Sneider*TEC017106S*43	TEC017106S	43
5	Isaiah Lee*TEC017102X	TEC017102X
6	Billy*Bob*3 Joel*TBF016052H*35	TBF016052H	35
Main

Cell Formulas
Range	Formula
B2:C2,B6:C6,B4:C4,B3,B5	B2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(A2,"*","</m><m>")&"</m></k>","//m[position()>=last()-1]"),IF(COUNT(f),TRANSPOSE(f),INDEX(f,2)))
Dynamic array formulas.

Hey Fluff!!
This worked for my table of about 80 rows, the sheet I am applying this to uses project names, not people names (due to confidentiality reasons, I changed them). So a few project names have "&", but on the rows with "&" within the name, the formula came with the error "VALUE".
(There are no "&" within the ID or age- just the names)

 

[Fluff]

Ok, try

=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,"*","</m><m>"),"&","&amp;")&"</m></k>","//m[position()>=last()-1]"),IF(COUNT(f),TRANSPOSE(f),INDEX(f,2)))

 

[EmilyCrandall]

Ok, try

=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,"*","</m><m>"),"&","&amp;")&"</m></k>","//m[position()>=last()-1]"),IF(COUNT(f),TRANSPOSE(f),INDEX(f,2)))

Thank you so much!!!! It worked!! I hope you have a fantastic day!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[EmilyCrandall]

Ok, try

=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,"*","</m><m>"),"&","&amp;")&"</m></k>","//m[position()>=last()-1]"),IF(COUNT(f),TRANSPOSE(f),INDEX(f,2)))

Is there a way I can delete the delimited data from the original cell, and just keep the two new columns?

 

[Fluff]

Only if you convert the formula to values.

 

[EmilyCrandall]

Only if you convert the formula to values.

Okay, thank you!