Combine LEFT and VLOOKUP Formulas in one cell
I need to combine two formulas in one cell. One formula removes a specific text from the body of the email and another formula maps it from another sheet via a VLOOKUP.
How would I combine these two formulas? Thank you!!
=LEFT(Text@row, FIND(">", Text@row, 1)  1)
=VLOOKUP([Interface Log]@row, {Interface Raw Data  Daily Range 2}, 5, false)
Best Answer

No problem at all!
In this instance, although we could write it in one function, the fastest way I can think of doing this is to set them up as two separate instances, like so:
=SUBSTITUTE(SUBSTITUTE(VLOOKUP([Interface Log]@row, {Interface Raw Data  Daily Range 2}, 5, false), "This Message is From an External Sender", ""), "Caution: Do not click links or open attachments unless you recognize the sender and know the content is safe.", "")
That should do it! 🙂
Answers

If using the LEFT formula to provide your search data for the VLOOKUP then this would be the formula.
=VLOOKUP(LEFT(Text@row, FIND(">", Text@row, 1)  1), {Interface Raw Data  Daily Range 2}, 5, false)
if you wanted to use the LEFT formula on the output of the VLOOKUP then this would be the formula
=LEFT(VLOOKUP([Interface Log]@row, {Interface Raw Data  Daily Range 2}, 5, false), FIND(">", Text@row, 1)  1)
I haven't tested the formula but it does appear that the structure is correct.
Hope that helps
Thanks
Paul

thanks, I am new to smartsheet (and formulas) so trying to figure out how to best explain. I did try both above, but they came up "unparsable". I want to basically pull in the value of the other cell without bringing in ">". I do not want that text to appear in my result. Not sure, would that make a difference? Thanks again.

Can you post a screen capture with your formula open in the cell (blocking out sensitive data)? Unparseable means that perhaps a column name is incorrect or there's a missing parentheses, etc.
The FIND function is finding the symbol and removing it from the value, I believe, but we'd need to see what formula you used.
Cheers!
Genevieve

Yes, thanks so much. See attached.

This image has different content than your original question  are you looking to simply remove the phrase "This message is from an external sender:" from every cell?
If so, I would actually use the SUBSTITUTE Function:
=SUBSTITUTE(VLOOKUP([Interface Log]@row, {Interface Raw Data  Daily Range 2}, 5, false), "This message is from an external sender:", "")
Which means that if this is the source sheet:
Then this is what's pulled in:
However this is only looking for that one phrase. If I've misunderstood, it would be helpful to know what you're using the original LEFT formula for, and if there's a consistent value you can search for in your "Body" in the source sheet.
Thanks!
Genevieve

Thank you so much! That did indeed work, however, I have a break in that text. Below the line is the complete text I want to remove. It will only remove the first line, not the second. Is there a way to include in that formula the break?
Thanks again, and apologizes for not being able to explain it properly.

"This Message is From an External Sender
Caution: Do not click links or open attach

Is that text all in one line in the cell?
"This Message is From an External Sender Caution: Do not click links or open attach"
Or is it broken up in multiple lines?
If it's one line, and will always appear with that exact phrasing, you can simply update the formula to include all that text:
=SUBSTITUTE(VLOOKUP([Interface Log]@row, {Interface Raw Data  Daily Range 2}, 5, false), "This Message is From an External Sender Caution: Do not click links or open attach", "")
Does that work for you?
Cheers,
Genevieve

thanks, it is broken into multiple lines. I have attached a screenshot with the text that I want to remove shown in the red box. The formula doesn't recognize the second line. Thanks again. You are fantastic!

No problem at all!
In this instance, although we could write it in one function, the fastest way I can think of doing this is to set them up as two separate instances, like so:
=SUBSTITUTE(SUBSTITUTE(VLOOKUP([Interface Log]@row, {Interface Raw Data  Daily Range 2}, 5, false), "This Message is From an External Sender", ""), "Caution: Do not click links or open attachments unless you recognize the sender and know the content is safe.", "")
That should do it! 🙂

You are a genius! It worked perfectly. Thank you!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!