Extracting person name from file name-CodePudding

I have a column of file names in the format {lastName}_{firstName}_{termInfo}_{Progress_Report/Transcript}_randomstringofcharacters.pdf

I would like to extract from this a variable that is lastName, firstName so that I can run a vlookup to match it to IDs. Is there an easy way to write a formula to do this?

Thanks!

CodePudding user response：

try:

=JOIN(", ", ARRAY_CONSTRAIN(SPLIT(A1, "{_}"), 9^9, 2))

for array:

=IFERROR(BYROW(A1:A, LAMBDA(x, JOIN(", ", ARRAY_CONSTRAIN(SPLIT(x, "{_}"), 9^9, 2)))))

CodePudding user response：

Option 01

=ArrayFormula(REGEXREPLACE(ARRAY_CONSTRAIN(SPLIT(A3,"_"),1,2), "\{|\}", ""))

Option 02

=ArrayFormula(BYROW(A2:A, 
 LAMBDA(rg, IFERROR(TEXTJOIN(", ",1,
 REGEXREPLACE(ARRAY_CONSTRAIN(SPLIT(rg,"_"),1,2), "\{|\}", "")),""))))