Display a city name based on a text

Hello everyone,

I want to display a city name based on a text, for example:

COLUMN A

T1 = Toulouse

T2 = Bordeaux

T3 = Paris

T4 = ...

A cell in column A can contain a single character (T1) or several (T1 + T3). In column B I would like to display the name of the city according to the characters but I can't do it to combine 2 characters. My formula that only works for a single character is:

=IF(A2="";"";IF(A2="T1";"Toulouse";IF(A2="T2";"Bordeaux";IF(A2="T3";"Paris";IF(. ...)

Ps: not possible to use SearchV in my file...

thanks in advance

Cordially, azerty64

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @azerty64

    You might want to use a helper sheet (call it "City Lookup") that contains your Text values and your City values:

    Then you can use a formula that works even if you add new Text and City combinations in the future.

    We'll use the JOIN, COLLECT, and CONTAINS functions in column B.

    =IF(ISBLANK(A@row); ""; JOIN(COLLECT({Reference to City Column in City Lookup sheet}; {Reference to Text column in City Lookup sheet}; CONTAINS(A@row, @cell)); ", ")

    Logic:

    If Column A in this row is blank, set column B in this row to blank; otherwise, collect the City from the City column in the City Lookup sheet from the rows where the Text column contains a value found within column A on this row.

    This should return some thing like this:


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/20/22 Answer ✓

    Unfortunately the first formula is not going to work. The logic in it is flawed, which was my mistake because I hadn't actually tried it out or done it before. Essentially, you're asking it to look in this:

    to see if it can match a value in the left column with this:

    The flawed logic says "find all the cells with single values in them that contain any one of the many values in this cell," but the function can't do that. The function only works the other way: find a cell with many values in it that contains this one value.

    I think the only way you might be able to do this without helper columns would be a long string of concatenated INDEX/MATCH statements looking for values matching the the text positions inside the Text column. Something like this:

    =INDEX({Lookup Sheet City Column}; MATCH(LEFT(Text@row, 2); {Lookup Sheet Text Column}; 0)) +" "+ INDEX({Lookup Sheet City Column}; MATCH(MID(Text@row, 4, 2); {Lookup Sheet Text Column}; 0)) +" "+ INDEX({Lookup Sheet City Column}; MATCH(MID(Text@row, 7, 2); {Lookup Sheet Text Column}; 0));....... keep going until you've accounted for 7 possible entries in the text field.

    (The +" "+ adds an empty space between each city name.)

    Making your text field a multi-select column would help ensure that a forgotten or extra space in the text field doesn't keep this formula from working.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!