I have a bunch of fields with Last Name, First Name, and want the order reversed and the comma removed. This topic fixes these perfectly: In Calc, is there a way to reverse the order of text in a cell? [closed] - Ask LibreOffice Problem is, I also have a bunch of fields in this format: Last Name#1, First Name#1; Last Name#2, First Name#2 etc. Sometimes it’s two names, sometimes three, the names are always seperated by semicolons. Thanks in advance for anyone that can help me here!
So if a cell contains Last Name#1, First Name#1; Last Name#2, First Name#2, then what do you want as a result? Please give a complete, reproducible example as recommended in guidelines for asking.
First Name#1 Last Name#1, First Name#2 Last Name#2, etc.
So change the semicolon to a comma? Or is that a cell break? A complete example would be something like: “Cell A1 contains xxx and the desired result is that cell B1 should contain yyy.” However, hopefully, my answer is general enough to cover whatever you need.
Actually just replacing the semicolons with nothing works in 90% of cases…darn simplest solutions! Thanks for replying.
If cell A1 contains:
Last Name#1, First Name#1; Last Name#2, First Name#2; Last Name#3, First Name#3; Last Name#4, First Name#4
…then select it and go to Data → Text to Columns. Mark to split by Semicolon and press OK.
A B C D
------------------------- ------------------------- -------------------------- -------
Last Name#1, First Name#1 Last Name#2, First Name#2 Last Name#3, First Name#3 L#4...
Now you can finish by applying the solution given in the link in the question to each cell.
To merge everything back together in a single comma-delimited cell, use a formula like this.
=TEXTJOIN(", ";;A1;B1;C1;D1)