Combine LEFT and VLOOKUP Formulas in one cell

BJIloveSmartSheet

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)

Genevieve P.

Hi @BJIloveSmartSheet

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! 🙂

Paul McGuinness

Hi @BJIloveSmartSheet

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

BJIloveSmartSheet

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.

Genevieve P.

Hi @BJIloveSmartSheet

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

BJIloveSmartSheet

Yes, thanks so much. See attached.

Formula for eliminating words wtih vlookup.docx

Genevieve P.

Hi @BJIloveSmartSheet

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

BJIloveSmartSheet

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

Genevieve P.

Hi @BJIloveSmartSheet

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

BJIloveSmartSheet

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!

2023-02-14_10-50-17.png

Genevieve P.

Hi @BJIloveSmartSheet

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! 🙂

BJIloveSmartSheet

You are a genius! It worked perfectly. Thank you!!