Auto split long text into two columns

Question

Level 1

9 points

Auto split long text into two columns

I'm doing a merge for some address labels. Several of the Company Names needs to be split into two columns so I can have them be on two lines, Company Name 1, Company Name 2. Same goes for the address lines, Address 1, Address 2.

Obviously I could manually go through all the rows and do this. I'd much rather find a shortcut.

Numbers does not seem to have a text to columns feature like excel.

Seems as though you could write a formula to cut a certain number of characters from Column A and place them into Column B.

Something like, if the number of characters in column A is greater than 15 then cut and paste into column B.

Something would have to be written in there to make sure words were not split.

Any ideas or formula writers out there? I'm a designer so this is not my area of expertise.

Thanks.

iMac 27″ 5K, macOS 10.14

Posted on Aug 20, 2020 3:02 PM

Reply

Answer 1

Best reply

SGIII

Level 8

35,534 points

Aug 21, 2020 2:02 AM in response to vondy21

Specifics would help. From your very general description you could try something along these lines.

Formula in B2, filled down:

=REGEX.EXTRACT(A2,"\b\S+\b",1) &" "& REGEX.EXTRACT(A2,"\b\S+\b",2)

This extracts the first two words.

Formula in C2, filled down:

=IFERROR(RIGHT(A2,LEN(A2)−LEN(B2)−1),"")

The extracts the rest, if any.

To extend the number of words to, say three, you would do this:

=REGEX.EXTRACT(A2,"\b\S+\b",1) &" "& REGEX.EXTRACT(A2,"\b\S+\b",2) &" "& REGEX.EXTRACT(A2,"\b\S+\b",3)

SG

Reply

Answer 2

vondy21 Author

Level 1

9 points

Aug 21, 2020 7:29 AM in response to SGIII

Wow thanks! That will cut my time down greatly.

There are a couple of issues I don't know if can be addressed, punctuation is not carrying over to column B where we take the first 2 or 3 words.

So:

Alive Hospice, Inc.

Column B: Alive Hospice Inc

Column C: .

No punctuation in column B but it carries over the period to column C.

It's doing something strange as well:

Home & Hospice Care Of Rhode Island Foundation

Column B: Home & Hospice Care

Column C: e Of Rhode Island Foundation

So it's carrying over the "e" from "Care" onto column C but also leaving it in Column B.

Don't worry too much about it. I can use this as a starting point. It still saves me a ton of time.

Thanks again!

Reply

Answer 3

SGIII

Level 8

35,534 points

Aug 21, 2020 10:09 AM in response to vondy21

vondy21 wrote:

punctuation is not carrying over to column B where we take the first 2 or 3 words.

So:

Alive Hospice, Inc.

Column B: Alive Hospice Inc

Column C: .

To keep the punctuation you could try something like this. Note there is a space after the ^. Within the brackets, this means match anything (including commas) except a space.

=REGEX.EXTRACT(A2,"[^ ]+",1) &" "& REGEX.EXTRACT(A2,"[^ ]+",2)

REGEX has endless variations! One should get you pretty close to what you need.

SG

Reply

Auto split long text into two columns

Similar questions