Help

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

33766 73
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
SarahB
5 - Automation Enthusiast
5 - Automation Enthusiast

Justin you are amazing, I hope you can help - I've read through these and whilst similar, I could not find a solution to my issue. I have a long string of text, and want to pull out a particular part;

EMAIL
Hey Bob, How's things?
Thank you for your email.
This is the text I want.

Please let me know if I can help.
Blah blah blah
blahblahblahblahblahbla
Blahblahblahblah

So there will be lots (random) of lines of text, but I want to pull out the text which will always be sandwiched between Thank you for your email. and Please let me know if I can help. 

Is this doable?

I've no idea what I'm doing, nor understand how regex works, but tried this ... and got an error

TRIM(REGEX_EXTRACT({EMAIL}, "(?:.*Thank you for your email. )(.*)(?:\\sPlease let me know if I can help.*)"))
 
Many thanks, Sarah
Julia_Barry
4 - Data Explorer
4 - Data Explorer

@Justin_Barrett Wow, thank you for all this! I've been able to get pretty far with my current conundrum just in reading this thread. My remaining question is: What would the formula be if I want to have something like "Nancy & Lindsey" ported into the first name field, when the full name field reads "Nancy & Lindsey Smith"? Thanks for your help!

Also curious if there's a formula that will shunt over a first name from full name column, if there's just a first name available? Right now it's putting the first name in the last name column because there are no spaces when there's just one name in the full name field. Ay!

@Julia_Barry I'm glad that my earlier solutions were useful for you! I'm sorry that I haven't responded sooner to your follow-up questions. I don't visit the forums any more for a variety of reasons, and the account where I receive email notices of private messages isn't used nearly as often as it used to be.

Re: your questions, at first glance they feel like they're at odds with each other. The first solution that comes to mind to solve the first problem would not work for the second situation, and vice versa. That said, I was able to come up with formulas for both first and last name fields that would work in a variety of use cases:

Screen Shot 2023-09-29 at 7.02.54 AM.png

Here's the First Name formula:

IF(
  {Full Name},
  IF(
    FIND("&", {Full Name}),
    REGEX_REPLACE({Full Name}, " [^ ]*$", ""),
    REGEX_EXTRACT({Full Name}, "^[^ ]*")
  )
)

...and the Last Name formula:

IF(
  {Full Name},
  IF(
    REGEX_MATCH({Full Name}, " |&"),
    REGEX_EXTRACT({Full Name}, "[^ ]*$")
  )
)