Skip to main content

Find Character String and return next x amount of characters

  • October 20, 2020
  • 2 replies
  • 45 views
D

I am trying to get values that follow a defined string in incoming emails.
Example: I have zapier setup to bring emails of interest in from UPS and I want to put the "tracking number in a new cell from that email.
FIND("Tracking No.: ",{UPS Email})+11
what i get in the cell is 11 not the next 11 characters that I want.

2 replies

P
Forum|alt.badge.img+9
  • pwa
  • Participating Frequently
  • October 21, 2020

Hi Dan, welcome to the board!

You’re on the right track with FIND(), but you need to combine it with the MID() function to extract text from {UPS Email}.

MID() takes three arguments:

  1. The string from which to extract.
  2. The starting position of the substring to extract.
  3. The length of the substring to extract.

You got the starting position with FIND(), but that will give you the position of the first character in "Tracking No.:", so you’ll need to add another 13 characters on to get the starting position of the tracking number.

Your end result will looking something like this:

MID({UPS Email}, FIND("Tracking No.:", {UPS Email})+13, 11)

D
  • Dan_Haugen
  • Author
  • New Participant
  • October 21, 2020

Hi Dan, welcome to the board!

You’re on the right track with FIND(), but you need to combine it with the MID() function to extract text from {UPS Email}.

MID() takes three arguments:

  1. The string from which to extract.
  2. The starting position of the substring to extract.
  3. The length of the substring to extract.

You got the starting position with FIND(), but that will give you the position of the first character in "Tracking No.:", so you’ll need to add another 13 characters on to get the starting position of the tracking number.

Your end result will looking something like this:

MID({UPS Email}, FIND("Tracking No.:", {UPS Email})+13, 11)


Thank you for your quick reply. I found that your suggestion is returning the 11 characters after the first 13 characters in the email. Further troubleshooting I trimmed “Tracking No.:” to “Tracking” and it found properly the first occurrence of the word tracking, however it seems the condition of the formula if the text is not found the offset starts from the first position.
When I inspected the element I found that the email (html) was this:
Tracking No.: 1Z54XW0605555552360
I tried to past that variable in and it did not find it.

Terms & ConditionsAccessibility statement