Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 11, 2019 05:19 PM
I have a string of text that I want to break into two separate strings of text.
Here’s an example:
I want all of the words that precede the “/” to be one string of text. And then I want all of the words that come after the “/” to be a separate string of text. I want to completely eliminate the “/”.
So, to recap, we start with this:
And then we end-up with this:
Mar 11, 2019 07:29 PM
If you want them in two fields, use this for the Before field (replace Name
with your own source field name):
LEFT(Name, FIND("/", Name) - 2)
…and this for the After field:
RIGHT(Name, LEN(Name) - FIND("/", Name) - 1)
If you want the two lines combined into one field on separate lines:
LEFT(Name, FIND("/", Name) - 2) & "\n" &
RIGHT(Name, LEN(Name) - FIND("/", Name) - 1)
Mar 12, 2019 08:11 AM
AMAZING! This totally worked. Thank You!
Jun 24, 2020 01:05 AM
I want to use this same formula; however, I need to separate the string of text after and before the first number (date in this case).
Example
Field 1 (original field):
Angola 8 April 1994
Antigua and Barbuda 30 March 1987
Field 2 (First Result):
Angola
Antigua and Barbuda
Filed 3 (Second Result)
8 April 1994
30 March 1987
Can you please support with this?
Thanks
Jun 24, 2020 09:41 AM
Are those two lines in a single record (i.e. a long text field), or are they two different records? The solution will depend on the answer to that question.
Jun 24, 2020 10:00 AM
No, Angola 8 April 1994 would be in one line, and Antigua and Barbuda 30 March 1987 in another below it.
I have over 100 lines/records with similar information that I need to split. But all of these lines have the same sequence. “COUNTRY” & “DATE”; for example Antigua and Barbuda 30 March 1987 and then in the next line I have Angola 8 April 1994, and the line after that South Africa 25 January 2020, and so on.
Jun 24, 2020 10:36 AM
Here’s what I came up with:
Note that the {Date}
field is using the default date formatting, but you can tweak it as you wish. Here’s the formula for the {Location}
field:
TRIM(SUBSTITUTE(Original, DATETIME_FORMAT(Date, "D MMMM YYYY"), ""))
And the {Date}
field:
DATETIME_PARSE(Original, "D MMMM YYYY")
Jun 25, 2020 08:41 AM
Hi Justin you are a SUPER STAR, thank you so much.
Aug 28, 2020 03:06 AM
Hello Justin, I have similar issue.
From cells with text followed with (number) I would like to extract just the number value to be able to sum in a new column.
I show you are very good in this so please help me!
See the example of what Im looking to do it.
Thank you very much!
Aug 28, 2020 08:22 AM
A couple questions come to mind:
I can think of a couple ways to go about this, but it will depend on the predictability of certain patterns in the data, which is why the answers to these questions are important.