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!