LAMBDA/SUBSTITUTE formula

[RonOliver]

Hi, geniuses,

This is a formula from long ago:

=SUBSTITUTE(TEXTSPLIT(REDUCE('ED'!A304,'BB'!$A$2:$ZZ$2,LAMBDA(s,r,IF(LEFT(s, LEN(r)+1) = r & " ", r & "#", SUBSTITUTE(s," "&r&" "," "&SUBSTITUTE(r," ","#")&"#"))))," "),"#"," ")

This is how this formula works: In A304, I got a name — "Del Jarro Oxley". In the range in the BB sheet, I got a list of prepositions, among which is "Del". This formula is meant to split A304 into "Del Jarro" and "Oxley". However, because "Del" is at the beginning of the string, the formula is not able to place a # before the word "Del" and will therefore split this into "Del", "Jarro", "Oxley". This formula would have worked, however, had it been "Oxley Del Jarro", as it would have split it into "Oxley" and "Del Jarro". Can you guys give me any ideas to work around this problem? This is actually part of a larger formula, so I would not want to change it entirely unless it is necessary. Thanks!

 

[hagia_sofia]

This probably does not match your requirement "not to change it entirely":

=LET(
array,'ED'!A304,
list,'BB'!$A$2:$ZZ$2,
SUBSTITUTE(DROP(REDUCE("",array,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(SUBSTITUTE(TEXTJOIN(" ",,IFERROR(BYCOL(TEXTSPLIT(b," "),LAMBDA(a,XLOOKUP(a,list,list&"#"))),TEXTSPLIT(b," "))),"# ","#")," ")))),1),"#"," "))

 

[RonOliver]

This probably does not match your requirement "not to change it entirely":

=LET(
array,'ED'!A304,
list,'BB'!$A$2:$ZZ$2,
SUBSTITUTE(DROP(REDUCE("",array,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(SUBSTITUTE(TEXTJOIN(" ",,IFERROR(BYCOL(TEXTSPLIT(b," "),LAMBDA(a,XLOOKUP(a,list,list&"#"))),TEXTSPLIT(b," "))),"# ","#")," ")))),1),"#"," "))

Did a short test and it works. You people are amazing. Will test this thoroughly through the weekend and mark this as the solution if it works. Thanks so very much!

 

[hagia_sofia]

Did a short test and it works. You people are amazing. Will test this thoroughly through the weekend and mark this as the solution if it works. Thanks so very much!

Thanks for the feedback; if you encounter a problem we will adjust it or find another solution...