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
-
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!
-
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
-
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!
-
great thanks a lot. 🤗
This is exactly what I needed!
Cordially;
azerty64
-
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?
-
Can you give me an example of how your Text field is formatted? Is your text field a multi-select drop down column?
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!
-
Yes it is a simple sheet with simple characters
-
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.
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!
-
In total there are 7 (T1 to T7)
-
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.
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!
-
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
-
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!
-
Hello Jeff
Thank you for your answer, it solved my problem. Good day to you.
Cordially,
azerty64
Help Article Resources
Categories
Check out the Formula Handbook template!
