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)

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

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

Answers

  • Paul McGuinness
    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

  • 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.
    Genevieve P. Employee Admin

    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin

    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

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


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!