You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
Question marked as Top-ranking reply

Posted on Aug 21, 2020 2:02 AM

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


3 replies
Question marked as Top-ranking reply

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


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!

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

Auto split long text into two columns

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.