Splitting a long field into two without chopping words

[akedm]

Hi,

Sample workbook attached.

I'm trying to take one long field, like a street address, and split it into fields. These fields can hold only so many characters. The trick is I do not want to split words, so if the default split is going to split a word in two, then I need to find the space before the default split, so I can split there instead.

Can this be done by formula. Or is this a macro situation?

Thanks.

[Gregb11]

Maybe

F3 and copied down:
=IF(LEN(A3)>20,TEXTBEFORE(LEFT(A3,20)," ",-1,,1),A3)

G3 and copied down:
=TEXTAFTER(A3,F3)

[AliGW]

Delete ALL expected results in F and G, then in F3:

=LET(a,IF(LEN(A3:A7)>20,TEXTBEFORE(LEFT(A3:A7,20)," ",-1,,1),A3:A7),b,BYROW(A3:A7,LAMBDA(r,TEXTAFTER(r,a))),HSTACK(a,b))

[kvsrinivasamurthy]

Try
In L3

Please Login or Register  to view this content.

[akedm]

Greg, nice! Ali, nice! KV...I dont know what to tell you - that's some next-level Gandalf wizardry going on there. Nice!

TLDR for anyone who wants the answer: They all work!

Greg: I just heard of TEXTBEFORE/TEXTAFTER moments before I wrote this question, and I was definitely making things too complicated for my own good. I didn't know these formulae could be manipulated in the way they could be with the extra arguments as you are showing me here. But I can read your formulae left-to-right, and they make sense now. Thank you!

Ali: I was going more in your direction with evaluating LET and so on, but I quickly drove myself into the weeds. I didnt know you could array LEN and LEFT and such. LEN(A1) was as range-y as I ever got. LEN(A3:A7) and LEFT(A3:A7...) - mind blown! I recall that for a brief moment months ago I understood LAMBDA and HSTACK and BYROW. I havent used those for a while and actually forgot they existed. They deserve my study. Thank you!

KV: You live in a tall wizard's tower high above the land, don't you? I'll make sense of what you did eventually. But as soon as I saw the opposing formulae TEXTSPLIT and TEXTJOIN side-by-side I knew right then that I was in deep, deep waters. It's a beautiful composition. Thank you so much!

[AliGW]

Just as an aside, KV's formula is not as efficient as mine and COULD fall over if the dataset grew very large.

Thanks for the rep.