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

[J_B_S]

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

[Justin_Barrett]

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)

[J_B_S]

AMAZING! This totally worked. Thank You!

[Tiaan_Terblanch]

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

[Justin_Barrett]

Tiaan_Terblanche:

Field 1 (original field):
Angola 8 April 1994
Antigua and Barbuda 30 March 1987

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.

[Tiaan_Terblanch]

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.

[Justin_Barrett]

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

[Tiaan_Terblanch]

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

[Unai_Ezkurra]

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!

[Justin_Barrett]

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.