Searching for a specific word in a cell and uploading it in another cell

Hello,

I have a big set of data, one of the columns are filled with information, that contains either "Local" or "Relocation" (sometimes it does not include the word at all). Example

Portugal Conversions LeadGen French Local 2023

Portugal Conversions LeadGen French Local 2023

Portugal Conversions Relocation French 2023

Portugal Conversions LeadGen Local Dutch 2023

Portugal Conversions LeadGen Local French 2023

Portugal Conversions LeadGen Local German2023

Portugal Conversions LeadGen French 2023

I am trying to build a formula that reads the cell's text and:

  1. If it finds "Local" - it populates it with "Local";
  2. If it finds "Relocation" - it populates it with "Relocation";
  3. If it does not find any, it leaves it empty.

I came up with this (the column I am going through is "Campaign"):

=if(isnumber(search("local",Campaign@row)), "Local", if(isnumber(search("relocation",Campaign@row)), "Relocation", " "))

But I get an error message #UNPARSABLE

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    SEARCH is not a valid function in Smartsheet. Try this instead:

    =IF(FIND("Local"; Campaign@row)> 0; "Local"; IF(FIND("Relocation"; Campaign@row)> 0; "Relocation"))

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    SEARCH is not a valid function in Smartsheet. Try this instead:

    =IF(FIND("Local"; Campaign@row)> 0; "Local"; IF(FIND("Relocation"; Campaign@row)> 0; "Relocation"))

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • I had to make some small edits ( , instead of ; ), but it worked. Thank you very much, Paul!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!