Text into Columns - Why is this so hard?

Question

Level 1

20 points

Text into Columns - Why is this so hard?

Here's what I have - Column A: Lastname, Firstname

Here's what I want - Column A: Lastname Column B: Firstname

I would like Column A to just have the last name, and column B to just have the first name. There is already a comma between the first and last (don't ask me how it got there). Without knowing how to code, I really have no clue how to do this and many of the solutions pertain to much older versions of Numbers that menus don't seem to have the same features anymore. It might be time for a new fix, or can someone please point me to the correct area? Thank you so much!! This is driving me crazy!

Posted on Aug 26, 2024 1:10 PM

Reply

Answer 1

Camelot

Level 9

55,329 points

Aug 26, 2024 1:47 PM in response to wpiacenti

Is this a one-time conversion thing? or something ongoing?

If it's a one-time then your simplest path is probably to create two new columns that extract the names, then copy the resulting columns and use Edit -> Paste Formula Result to copy the plain-text (vs. calculated) values.

For example, assuming Column A has the names in Lastname, Firstname format:

Set Cell B2 to:

=TRIM(TEXTBEFORE(A2,","))

and set cell C2 to:

=TRIM(TEXTAFTER(A2,","))

TEXTBEFORE() and TEXTAFTER() split a string based on a given character (in this case ",") and return either the preceding or following text. The addition of the TRIM() function strips any leading or trailing spaces.

Now you should have three columns.

If the data is dynamic, you can leave column A in place (optionally hide if if you like). Or you can copy columns B and C and use Paste Formula Result into new columns that will just have the text and not the calculation relying on column A.

Reply

Answer 2

wpiacenti Author

Level 1

20 points

Aug 26, 2024 4:39 PM in response to Camelot

Thank you!! This is great!

Reply