CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Rebecca Sacks
Rebecca Sacks

Reputation: 21

Extracting person name from file name

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!

Upvotes: 0

Views: 68

Answers (2)

Osm
Osm

Reputation: 2881

Option 01

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

enter image description here

Option 02

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

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

try:

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

enter image description here

for array:

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

enter image description here

Upvotes: 3

Related Questions