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!![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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!