Ask Your Question
0

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

asked 2019-01-03 14:13:03 +0200

NoLogo gravatar image

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.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2019-01-03 15:17:36 +0200

Grantler gravatar image

updated 2019-01-04 01:14:19 +0200

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

edit flag offensive delete link more

Comments

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 :)

SM_Riga gravatar imageSM_Riga ( 2019-01-03 15:26:51 +0200 )edit

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

Grantler gravatar imageGrantler ( 2019-01-03 15:39:38 +0200 )edit

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?

goonhilly gravatar imagegoonhilly ( 2019-05-04 19:20:34 +0200 )edit
0

answered 2019-01-03 15:11:24 +0200

updated 2019-01-03 15:18:16 +0200

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.

texttocolumns

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.

edit flag offensive delete link more
0

answered 2019-01-04 17:29:01 +0200

NoLogo gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-01-03 14:13:03 +0200

Seen: 512 times

Last updated: Jan 04