Help

Re: Formula to break a string of text onto 2 separate strings of text

11131 2
cancel
Showing results for 
Search instead for 
Did you mean: 
J_B_S
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a string of text that I want to break into two separate strings of text.
Here’s an example:

  • This is an example string of text / I want to break it into two separate strings of text

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:

  • This is an example string of text / I want to break it into two separate parts

And then we end-up with this:

  • This is an example string of text
  • I want to break it into two separate parts
73 Replies 73

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)

29%20PM

AMAZING! This totally worked. Thank You!

Tiaan_Terblanch
6 - Interface Innovator
6 - Interface Innovator

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

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.

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.

Here’s what I came up with:

Screen Shot 2020-06-24 at 10.33.43 AM

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

Hi Justin you are a SUPER STAR, thank you so much.

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!

Screenshot 2020-08-28 at 12.05.32

A couple questions come to mind:

  • Are there always going to be two numbers to extract?
  • Are the numbers always going to be single digits (i.e. less than 10)?

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.