Display a city name based on a text

azerty64

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

Jeff Reisman

@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:

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316750#Comment_316750

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.

Jeff Reisman

@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:

azerty64

great thanks a lot. 🤗

This is exactly what I needed!

Cordially;

azerty64

azerty64

@Jeff Reisman

I went a little fast because what you offer corresponds to my expectations. But in practice it doesn't work for me. For single text it works but for multiple texts not. I'm sorry, can you capture your formula in smartsheet for me please?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316593#Comment_316593

Can you give me an example of how your Text field is formatted? Is your text field a multi-select drop down column?

azerty64

Yes it is a simple sheet with simple characters

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316615#Comment_316615

How many different T# values do you have? At the most, how many different T# values would likely be together in the text field for a row?

If it's only a handful, we could do this with some hidden helper columns.

azerty64

In total there are 7 (T1 to T7)

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316623#Comment_316623

It's kind of messy, but you could do 7 hidden helper columns, each one looking for a T# value in the Text field, and then JOIN your results together:

"Toulouse" column:

=IF(CONTAINS("T1"; Text@row); "Toulouse"; "")

"Bordeaux" column:

=IF(CONTAINS("T2"; Text@row); "Bordeaux"; "")

etc.

Then, in your B column, use

=JOIN(Toulouse@row:Marseille@row; ", ")

to join all your city values together, separated by commas.

azerty64

https://community.smartsheet.com/discussion/comment/316650#Comment_316650

Excuse me but I think I prefer the first formula. I just want to know why I can't get it to work? On the photo we can see in column F that only the "text city" (T1) stands out. When there is T1+T3 for example it does not work.

Sorry for my english...

Cordially,

azerty64

Jeff Reisman

https://community.smartsheet.com/discussion/comment/316750#Comment_316750

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.

azerty64

https://community.smartsheet.com/discussion/comment/316815#Comment_316815

Hello Jeff

Thank you for your answer, it solved my problem. Good day to you.

Cordially,

azerty64