Hello everyone.
I have a field on the table which records several phone numbers to dial.
The phone numbers are composed by 10 digits, like XXFFFFFFFF, where XX range from 11 until 99. For every XX, there is a city represented, e.g., 11 = Sao Paulo, 21 = Rio de Janeiro.
I am trying to create another column where I can identify which city the number is from, e.g., if the number is 1133333333, than, the column would receive Sao Paulo.
I wrote the below formula, but it is not working.
Can someone give me a hand on this?
Thanks,
Column =
SWITCH (
TRUE (),
SEARCH ( "11", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "12", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "13", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "14", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "15", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "16", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "17", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "18", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "19", database[phone_number], 1, 0 ) = 1, "São Paulo",
SEARCH ( "21", database[phone_number], 1, 0 ) = 1, "Rio de Janeiro",
SEARCH ( "22", database[phone_number], 1, 0 ) = 1, "Rio de Janeiro",
SEARCH ( "24", database[phone_number], 1, 0 ) = 1, "Rio de Janeiro",
SEARCH ( "27", database[phone_number], 1, 0 ) = 1, "Espírito Santo",
SEARCH ( "28", database[phone_number], 1, 0 ) = 1, "Espírito Santo",
SEARCH ( "31", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "32", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "33", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "34", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "35", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "37", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "38", database[phone_number], 1, 0 ) = 1, "Minas Gerais",
SEARCH ( "41", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "42", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "43", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "44", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "45", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "46", database[phone_number], 1, 0 ) = 1, "Paraná",
SEARCH ( "47", database[phone_number], 1, 0 ) = 1, "Santa Catarina",
SEARCH ( "48", database[phone_number], 1, 0 ) = 1, "Santa Catarina",
SEARCH ( "49", database[phone_number], 1, 0 ) = 1, "Santa Catarina",
SEARCH ( "51", database[phone_number], 1, 0 ) = 1, "Rio Grande do Sul",
SEARCH ( "53", database[phone_number], 1, 0 ) = 1, "Rio Grande do Sul",
SEARCH ( "54", database[phone_number], 1, 0 ) = 1, "Rio Grande do Sul",
SEARCH ( "55", database[phone_number], 1, 0 ) = 1, "Rio Grande do Sul",
SEARCH ( "61", database[phone_number], 1, 0 ) = 1, "Distrito Federal/Goiás",
SEARCH ( "62", database[phone_number], 1, 0 ) = 1, "Goiás",
SEARCH ( "63", database[phone_number], 1, 0 ) = 1, "Tocantins",
SEARCH ( "64", database[phone_number], 1, 0 ) = 1, "Goiás",
SEARCH ( "65", database[phone_number], 1, 0 ) = 1, "Mato Grosso",
SEARCH ( "66", database[phone_number], 1, 0 ) = 1, "Mato Grosso",
SEARCH ( "67", database[phone_number], 1, 0 ) = 1, "Mato Grosso do Sul",
SEARCH ( "68", database[phone_number], 1, 0 ) = 1, "Acre",
SEARCH ( "69", database[phone_number], 1, 0 ) = 1, "Rondônia",
SEARCH ( "71", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "73", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "74", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "75", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "77", database[phone_number], 1, 0 ) = 1, "Bahia",
SEARCH ( "79", database[phone_number], 1, 0 ) = 1, "Sergipe",
SEARCH ( "81", database[phone_number], 1, 0 ) = 1, "Pernambuco",
SEARCH ( "82", database[phone_number], 1, 0 ) = 1, "Alagoas",
SEARCH ( "83", database[phone_number], 1, 0 ) = 1, "Paraíba",
SEARCH ( "84", database[phone_number], 1, 0 ) = 1, "Rio Grande do Norte",
SEARCH ( "85", database[phone_number], 1, 0 ) = 1, "Ceará",
SEARCH ( "86", database[phone_number], 1, 0 ) = 1, "Piauí",
SEARCH ( "87", database[phone_number], 1, 0 ) = 1, "Pernambuco",
SEARCH ( "88", database[phone_number], 1, 0 ) = 1, "Ceará",
SEARCH ( "89", database[phone_number], 1, 0 ) = 1, "Piauí",
SEARCH ( "91", database[phone_number], 1, 0 ) = 1, "Pará",
SEARCH ( "92", database[phone_number], 1, 0 ) = 1, "Amazonas",
SEARCH ( "93", database[phone_number], 1, 0 ) = 1, "Pará",
SEARCH ( "94", database[phone_number], 1, 0 ) = 1, "Pará",
SEARCH ( "95", database[phone_number], 1, 0 ) = 1, "Roraima",
SEARCH ( "96", database[phone_number], 1, 0 ) = 1, "Amapá",
SEARCH ( "97", database[phone_number], 1, 0 ) = 1, "Amazonas",
SEARCH ( "98", database[phone_number], 1, 0 ) = 1, "Maranhão",
SEARCH ( "99", database[phone_number], 1, 0 ) = 1, "Maranhão",
"UNKNOWN"
)
