To improve a formula concerning the identification of telephone prefixes

[stefanoste78]

Good afternoon.

I have a problem with a formula.
In sheet 1 I have telephone numbers and I should understand if these are fixed or mobile telephone numbers.
I created a list of fixed and mobile prefixes used in my state with the indication of the prefix in the first column and in column d the type of fixed or mobile prefix (this with reference to the other sheet).

The problem is that if you make a find you will notice that the numbers in sheet 1 should be fixed and not mobile.
How come you have this problem. Is there a change to be made in the formula?

Another question:
In the list of prefixes and in relation to mobile ones, the prefix may be preceded by the international prefix +39 (sometimes it can also be 39 only). In this case, could it be taken into account in the formula that the mobile prefix could have the international prefix?

Thanks

 

[Chihiro]

Not sure why you are using "*" in your MID function, and why you are doing reverse search from list to string.

You should do the reverse.
Ex:
=IFERROR(INDEX('prefissi telefonici'!$D$2:$D$286,MATCH(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A2," ",REPT(" ",99)),"+",""),99)),'prefissi telefonici'!$A$2:$A$286,0)),"")

 

[stefanoste78]

hi chihiro.
I tried your formula but it does not return mobile numbers.
the international prefix is +39 but sometimes it may not be indicated as there could only be 39 without + (rare case).
Then you have to take into account numbers that have different international prefixes and numbers that have a prefix not shown in the list.

 

[Chihiro]

That's because in this sample you have some oddity in your source data.

It appears as though numbers are stored as text, but it's stored as number. You can't match text against number.

When I enter a cell and confirm on "prefissi telefonici", it correctly returns as text type.
I checked here using =ISNUMBER(Cell)


As with any data analysis / calculation. Key is always to understand source data type(s) and perform any clean up or transformation before you perform further operation.

As for "+" rather than trying to handle it in single formula. I'd just add 2nd step to catch those special cases.

 

[stefanoste78]

On the prefixes sheet you can find fixed numbers formatted as text to avoid making the initial zero disappear.
I tried to transform mobile prefixes into text too, but nothing changes. Why?
as for the + I thought I'd copy the list of mobile prefixes and paste them below with the addition of the +

 

[Chihiro]

Anything below fisso isn't formatted as text. That's your issue.

What you need to do is select A234:A236 (or entire A column).

Data -> Text to Columns. Choose Delimited. And hit next until Step 3 of 3. There select column data format as Text and finish.

 

[stefanoste78]

the transformation was successful
Is there a way to find in the formula column the numbers that are not fixed or mobile in the column where the telephone numbers are present to distinguish them from the empty cells?

 

[Chihiro]

Not sure what you mean?
May be change "" in IFERROR argument to "SomeText".

 

[stefanoste78]

i have to add the if.


if the formula gives value then writes the value otherwise leaves empty if the cell is empty or if it is full, I write in the word ‘different number’.

that way I can tell the three cases.