VBA XLOOKUP to use text before semicolon

[rook]

Hello,

I am looking for assistance with following scenario. Please note that I am not able to attach sample Excel files, but I would appreciate any advice / direction.

I have following code - if cell is blank, it takes relative value RC[1] and creates XLOOKUP code that pulls value from EXAMPLE.xlsx workbook. It populates blank cells with the function, and this function shows values pulled from EXAMPLE.xlsx.

Dim rng As Range
Dim Cell As Range
lRow = Range("L" & Rows.Count).End(xlUp).Row
Set rng = ActiveSheet.Range("M2:M" & lRow)
rng.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=XLOOKUP(RC[1],EXAMPLE.xlsx!C2,SL.xls!C1,,1,1)"


This code is working, but I need a solution to following scenario:

If in cell M2 my data looks like this:

TEXT1; TEXT2
(I mean: TEXT1[semicolon and space]TEXT2)

I would need the code to use only TEXT1 (text before semicolon) as XLOOKUP value... Is it achievable with RC[1]?

The logic is:

If L2 is blank, take M2 and use it as XLOOKUP value and take into consideration that:
If M2 contains ";" (semicolon), take only string before ";" and use it as XLOOKUP value.

Thank you in advance for any help!

[SamT]

Sub Example()
Dim Wrd as String, Str As String
Str = "Text1;Text2"

Wrd = Split(Str, ";", 1, 1)
'Wrd = "Text1"

Wrd = Split(Str, ";")(1)
Wrd = "Text2"

End Sub

[rook]

Sub Example()
Dim Wrd as String, Str As String
Str = "Text1;Text2"

Wrd = Split(Str, ";", 1, 1)
'Wrd = "Text1"

Wrd = Split(Str, ";")(1)
Wrd = "Text2"

End Sub

Thank you for the answer! I might not have been too clear - I am looking for a way to implement an "extract" option in the existing code. Sometimes the cell in range M contains only one term ued as a xlookup value, sometimes it contains text separated by semicolon. In the second case I need the code to use only text before the semicolon and use it as xlookup value. So I am trying to combine FormulaR1C1 with some sort od text extraction... But not sure if this is even possible...