Calc: Find & delete last- or first certain amount of positions in a line of text

[NoLogo]

Hi, I have a list of names of people in one column, where this first and last names are written within in one cell as:

last name, first name(s)

I would like to change that to:

first name(s), last name

What I am thinking of is:

Create two columns with the same list of names
Column one: find and delete all comma’s with all text following
Column two: find and delete all comma’s with all text preceding

But I have no idea how to do this and I could not discover how to do such a thing with the Search & Replace in the GUI.

[SM_Riga]

Hello @NoLogo

There are few ways of doing this:

Assuming column A contains text string last name, first name(s), place in B column formula =MID(A1;SEARCH(",";A1;1)+2;100)&", "&LEFT(A1;SEARCH(",";A1;1)-1) or =RIGHT(A1;LEN(A1)-SEARCH(",";A1;1)-1)&", "&LEFT(A1;SEARCH(",";A1;1)-1) This will return text string first name(s), last name

or

Assuming column A contains text string last name, first name(s), select whole A column, go to menu item Data -> Text to Columns, select Comma as separator and you will get First Names and Last Names in separate columns. The you can use Concatenate() function or “&” operand to concatenate string in desired order.

In both cases you will probably need to use also TRIM() function to remove leading/trailing whitespaces. Also consider the fact, that formula will create a new copy of your data in adjacent column, Text to Columns dialogue will edit existing data. So backup highly recommended before practising.

[Grantler]

Copy column including first and last names. Select one of the columns.

In SEARCH&REPLACE activate Regular Expressions.

FIND: (^.*), (.*)

REPLACE: $1 (for the first part of name; before comma space)

REPLACE: $2 (for the second part of name; behind comma space)

So you have one column with first name and one with last name.

To connect both within one cell you can use the & character which connects text; it could be a formula like =B1&", "&A1

Cheers

Oh, now checked the reply containing formula: probably easier than my solution!

Upd.

FIND: (^.*), *(.*)
REPLACE: $2, $1

Without help column, direct changing/replacing

[SM_Riga]

I think you answer is more precise and even easier to implement and @NoLogo also mentioned Search & Replace functionality, which I did not mentioned. I just like formulas

[Grantler]

Text to Columns (including trimming) seems simpler than RegEx.

[goonhilly]

Can someone explain or point to help link on why in the above examples and others I have seen the white spaces are left between FIND: (^.), (.)
=B1&", "&A1
both the above have comma space before the next part of the function?
I have been looking at RegEx tutorials and cant see this explained.
The final line above
IND: (^.*), (.) REPLACE: $2, $1
it is difficult to see if there are indeed 1 or 2 white space after for example " $2, $1 although it appears to me that are 2 spaces?

[NoLogo]

Thank you for your help! The answer by SM_Riga has my preference as a solution. I’d really wish that LibreOffice would expand the internal Search & Replace function, as it could safe so much time in productivity. Especially if you could create and save your most used rules. Currently, the experience is often more similar to programming.