How to replace a comma followed by a space by a carriage return

Question

Level 1

12 points

How to replace a comma followed by a space by a carriage return

Hello,

I have many cells in numbers which contains a list of first names separated by a comma and a space. I would like to replace the ", " by a carriage return where the expected results is that each first name are listed on different row within the same cell.

The final outcome is to export the number worksheet as an rtf file where each first name are then on a different row.

Thanks in advance!

Posted on Apr 23, 2023 1:21 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,348 points

Apr 23, 2023 3:35 PM in response to Zoomdan

Here are two ways, starting with what you requested (but the second way is easier)

Formula in Cell A2 in the Expected table is

=SUBSTITUTE(Current::A2,", ",CHAR(8232))

fill down to complete the column

It replaces comma space with a Unicode character 2028 (8232 decimal), which is a line separator.

At the bottom of the Expected table add a footer row and put into it the formula

=TEXTJOIN(CHAR(8232),TRUE,A)

You can copy/paste that cell to an RTF document. You may have to remove a quote at the top and bottom (depending on what editor you paste into) and may have to remove some blank lines if you had any blank rows in the table. If it pastes into the document as a table cell, paste it into the TextEdit app as an intermediate step.

Second (easier) way:

Copy the column from the Current Table
Paste into a new TextEdit document that is set to Plain Text (in the Format menu). The table structure will be removed, leaving the text.
Use Find/Replace to replace all comma space with option Return
Format the document as RTF (in the Format menu) or copy/paste the list to your RTF editor.

Reply