Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 20, 2020 04:13 PM
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.
Oct 20, 2020 10:23 PM
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:
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)
Oct 21, 2020 11:26 AM
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.