8 Replies Latest reply on Oct 17, 2018 10:57 PM by Tim Guth

Split/Cut text after last 2 delimiters

Tim Guth Oct 17, 2018 6:57 PM

Hey Guys,

I am currently facing some data management problems. I got a file like the following:

name, age/year born, body fat%, height, weight

James Smith 43 year old 18%, 180, 86

Thomas Miller 1980 16.5%, 187, 81

Dean Thomas, 36 year old 13.1%, 174, 71

I need a new column with only "name, age/year born, body fat%" and cut off after the last 2 delimiters (commas in this case). As you see in the data, I cannot go from the left side since sametimes name, age/year, and body fat are separated by a comma, sometimes not. I cann only rely on height and weight being separated by a comma. I would like to have a column like this -->

James Smith 43 year old 18%

Thomas Miller 1980 16.5%,

Dean Thomas, 36 year old 13.1%

Which code would I need for it? I tried first with a custom split but the main column remains and doesn't delete the last 2 information delimited by the comma.

Thanks a lot for your help!

Best

1. Re: Split/Cut text after last 2 delimiters

Deepak Rai Oct 17, 2018 7:03 PM (in response to Tim Guth)

Split( your column, “,”,1)
Like Show 0 Likes(0)

Actions
2. Re: Split/Cut text after last 2 delimiters

Tim Guth Oct 17, 2018 7:26 PM (in response to Deepak Rai)

Hey Deepak,

that won't work since I stated that the information I want to extract is sometimes also delimited by a comma. So I have to choose a way to split everything before the LAST 2 comma.
Like Show 0 Likes(0)

Actions
3. Re: Split/Cut text after last 2 delimiters

Deepak Rai Oct 17, 2018 8:45 PM (in response to Tim Guth)
Hi Tim,
Here it is:
Thanks
Deepak
If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

SPLIT_FINDNTH.twbx 16.3 KB

2 of 2 people found this helpful
Like Show 2 Likes(2)

Actions
4. Re: Split/Cut text after last 2 delimiters

Tim Guth Oct 17, 2018 9:06 PM (in response to Deepak Rai)

Perfect,

thanks a lot!
Like Show 0 Likes(0)

Actions
5. Re: Split/Cut text after last 2 delimiters

Deepak Rai Oct 17, 2018 8:54 PM (in response to Tim Guth)

Welcome!!! Please Close The Thread also by Marking my Reply as CORRECT. This would Help Others. The CORRECT button is under my Reply in your Main Thread, not in email.
Thanks
Deepak
Like Show 0 Likes(0)

Actions
6. Re: Split/Cut text after last 2 delimiters

Tim Guth Oct 17, 2018 9:06 PM (in response to Deepak Rai)

Marked as correct

What if I have a case like

James Smith 43 year old 18%, "1,80", 86

in between all my observation, but I don't want to pay respect to the comma in the ". Do I need a regular expression for this?
Like Show 0 Likes(0)

Actions
7. Re: Split/Cut text after last 2 delimiters

Deepak Rai Oct 17, 2018 9:09 PM (in response to Tim Guth)

So what should be output in this case?
Like Show 0 Likes(0)

Actions
8. Re: Split/Cut text after last 2 delimiters

Tim Guth Oct 17, 2018 10:57 PM (in response to Deepak Rai)

Long String Field
James Smith 43 year old 18%, "1,80", 86
Thomas Miller 1980 16.5%, 187, 81
Dean Thomas, 36 year old 13.1%, 174, 71

Desired Outcome:
James Smith 43 year old 18%
Thomas Miller 1980 16.5%
Dean Thomas, 36 year old 13.1%

In your code you defined to cut off (-2) after the second last comma. However, the comma is now part of the text indicated with "text". If I use your formula, it cuts of like this: James Smith 43 year old 18%, "1 but desired is James Smith 43 year old 18%
Like Show 0 Likes(0)

Actions