Distinguish if number is a mobile or Landline number

March 13, 2020 at 05:30:49
Specs: Windows 10
Hi,
I have a spreadsheet that has phone numbers displayed as +44xxxxx. I need to determine if the number is a mobile or landline and I need it to state this in another column.

I have tried various formulas, but none work. Can someone help me with a formula please?

thanks
Emma


See More: Distinguish if number is a mobile or Landline number

Reply ↓  Report •

#1
March 13, 2020 at 05:52:57
How would that be possible, when cellphone companies are allowing landline numbers to be transferred?

"Channeling the spirit of jboy..."


Reply ↓  Report •

#2
March 13, 2020 at 07:12:26
How would any type of formula be able to do that? How many numbers do you have to check? If it's not a lot, just Google them or use Anywho: https://www.anywho.com/reverse-phon...

Reply ↓  Report •

#3
March 13, 2020 at 07:50:52
How are determining what is a Mobile Number and what is a Land Line Number?

Are you using the +44 string?

Are you including the + sign in your phone number list?

If all your using is the +44 then we should be able to give you a formula, but need
more info on what you list looks like.

Something like this should work:

=IF(LEFT(A1,3)="+44","mobile","not mobile")


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Related Solutions

#4
March 13, 2020 at 08:12:25
To expand on Mike's point, there needs to be something within the strings that are being evaluated that differentiates the mobile numbers from the landline numbers.

If all you have is a string of digits in each cell, with nothing that the formula can extract that definitively tells Excel that it's a cell phone or landline, we aren't going to be able to help.

As Mike said, we need more info.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
March 13, 2020 at 09:54:38
re; How would that be possible, when cellphone companies are allowing landline numbers to be transferred?

In the UK (and I think it's the same in Europe) landline numbers are a different composition/format to landlines. Whereas in USA/Canada both cell/mobiles and landlines use the same format/style of number. When one calls any number in the USA/Canada (certainly from overseas) one has no way to know if it's a landline or cell/mobile number; whilst in the UK it's very obvious which is which.

UK cell/mobiles usually in the form of: 07xxx-yyyyyy

Landlines are usually: 01xxx-yyyyyy - may be more or fewer numbers or after the initial (01xxx) exchange part of the number...

If calling one from outside of the UK then one again drops the default first (zero) after dialing the obligatory (country code) 44. Thus one dials: 44-7xxx-yyyyyy

And for a landline it's the same routine - drop the first zero (0) after dialing the 44

So in the UK at least it's relatively easy to know what type of phone you're calling...

That first zero (0) engages what used to be called (in the UK) the pay tone - starts the timer to determine how much to charge for the call (if any applied).

Obviously it's not required when the call originates from outside the UK.

message edited by trvlr


Reply ↓  Report •

#6
March 13, 2020 at 10:36:17
trvllr,

thanks for the breakdown.

If I understand you,
then the string 44-7 will get me a Mobile phone,
while the string 44-1 will get me a Landline.

So a modified formula of:

=IF(MID(A1,4,1)=7,"mobile",IF(MID(A1,4,1)=1,"Landline","Unk"))

should work for numbers in the UK. Yes?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#7
March 13, 2020 at 12:18:47
https://freecarrierlookup.com/
is a manual way to get the info.

Reply ↓  Report •

#8
March 13, 2020 at 13:29:06
Yup...

then the string 44-7 will get me a Mobile phone,
while the string 44-1 will get me a Landline

Can’t comment on your formulae, as I’m no genius there (if anywhere). But based on your past expertise here I suspect it will?

Free phone numbers in the UK start 08xx and may or may not be reachable from outside the UK by the standard 44-800 etc.; I’ve never tried it. Expensive premium rate numbers - think tv show voting numbers, “adult” content phone numbers etc. - they start 09xx. Again no idea if they’re accessible via 44-0900....


Reply ↓  Report •

#9
March 13, 2020 at 18:38:21
Ok, my bad. Didn't realize that it could be different formatting outside the US. And you're correct, over here it's standard 3-digit area code then 7-digit number...

"Channeling the spirit of jboy..."


Reply ↓  Report •

#10
March 13, 2020 at 19:02:04
Sometimes the Brits make things simple... and then again sometimes not.

I have a pheeling that the continentals (the EEC lot) have a variation on the Brit system; just add spice to it all...


Reply ↓  Report •

#11
March 13, 2020 at 19:45:43
re: " And you're correct, over here it's standard 3-digit area code then 7-digit number..."

e.g. (123) 345-6789

The problem is that the "standard 3-digit area code" (123) as well as the 3 digit prefix (345) might not be a landline or a mobile phone. It just might be a computer made to look like a telephone number, even a phone number with your own area code and 3 digit pre-fix.

Can you say "spoof"? ;-)

message edited by DerbyDad03


Reply ↓  Report •

#12
March 14, 2020 at 07:02:37
"...just might be a computer made to look like a telephone number, even a phone number with your own area code and 3 digit pre-fix."

And your own number for that matter. Had several of those when I had a landline. Just last month got a cellphone that works from home (thus dropping landline service) and while there's been a couple of spoofs, I'm still (old &) wise enough to not answer something I don't recognize. Those get blocked as soon as they finish....

"Channeling the spirit of jboy..."

message edited by T-R-A


Reply ↓  Report •

#13
March 14, 2020 at 07:08:55
I believe that the fake area code DerbyDad used (123) is
outside the rules because it begins with a "1". An area code
or phone number can begin with any digit except 0 or 1.

It used to be that all area codes had either a 0 or 1 as the
second digit, but that rule no longer applies. It only allowed
160 different area codes, which isn't enough any more.

-- Jeff, in Minneapolis


Reply ↓  Report •

#14
March 14, 2020 at 07:34:56
re' fake codes...

I think DD was simply using that as an example - well are that in the Canada/USA the first digit of the area code wouldn't be "1"?

This yahoo discussion clarifies the use of "1" and "0" in Canada/USA. It's a wee bit different in the UK - wouldn't ya know...

https://answers.yahoo.com/question/...

In the UK it certainly wouldn't be "1" as the first digit; that "has" to be the obligatory zero; to engage the pay tone system (when calling from within the UK).

It's quite common place in the UK (and in Canada/USA too) to have ROBO calls looking like genuine looking numbers. Usually landlines - but I have one or two that emulated cell/mobiles numbers as well. They arrive either on my landline or cell/mobile... One even used a genuine number (actually a florist in SW England/Devon). When I checked who it was who had called (usuall online trawls) I discovered that it was that florist. When I called them to check if they had called me (I have friends in their area and thought it might have been to do with something they were arranging?) - they had no knowledge of the the call at their end; they had certainly not called me. And there have also been other similar examples from what I've read online.

You can buy preprogrammed cards/sticks with a huge range of "genuine" looking numbers to fire out fake/scam/ROBO calls day and night. If person called picks up an answers and says one of several target words/phrases.. they are instantly connected to a live spam/con artist...


Reply ↓  Report •

#15
March 14, 2020 at 10:21:22
NB: sorry but this post seems to deviated significantly from the OP's question..."

"You can buy preprogrammed cards/sticks with a huge range of "genuine" looking numbers to fire out fake/scam/ROBO calls day and night."

The problem here in the states is that we've relied too heavily on CallerID since it's inception. It became easy to spoof, and we had believed that it was a panacea to stop all unwanted calls. As soon as the government came out with the "do not call" list, we then believed that it would be the solution. None of this put the onus on the telcos like it should have. With the current technology available, there's no reason any major telco shouldn't be able to flag spam calls before they're delivered....

"Channeling the spirit of jboy..."

message edited by T-R-A


Reply ↓  Report •

#16
March 14, 2020 at 12:23:48
I agree the Caller ID fiasco and how easy it has become to spoof... Likewise the "do not call listing in the US/Canada.

In the UK there is the infamous TPS - Telephone Preference Service. Allegedly once you register a given number (land line or cell/mobile) unwanted, unsolicited etc.calls are supposed to stop...

All sales and marketing, surveys, charities or whatever are "supposed" to be bound by law to not call that number; without your specific permission they may do so - and which permission can be changed at any time..

Naturally the rogues, spammers, con-artistes pay very strict attention to this; and they obey the rules...

There is an equivalent for junk, unsolicited mail too; and again of course the above groups pay close attention to that and obey the rules... The Roya lMail makes a lot of money delivering junk mail...; all of which goes into the scrap bin and may or may not be recyclable...

(ummm - looks like I've had too much - or not enuff - cooking sherry today if I really believe that...)

Hitting (prosecuting and fining) the occasional nuisance source doesn't really stop all of this stuff; they simply relocate the servers outside the UK and get away with it...

The telcos don't really care; preferring to sell the customer a magic programmable box to filter out pest calls. But as for actually fixing the problem their end.. one is whitsling dixie there I suspect...?


Reply ↓  Report •

#17
March 16, 2020 at 02:09:57
Hi Mike,

thats great, thank you. I will give that a go.

sorry, I wasn't clear in my original thread. They are all UK numbers, so either all starting 07 or 02,03 etc (so +447 or +442. There are over 15,000 phone numbers and I want an easy way to state if mobile or not.


Reply ↓  Report •

#18
March 16, 2020 at 04:35:36
Presumably "Emma1" is dealing with Greater London numbers only?

Remember that it's only in the Greater London area that landline numbers start with 0207 or 0208. Elsewhere numbers all start with 01xx or even 03xx.

re' 03xx area code(s)

https://www.ofcom.org.uk/phones-tel...

and this link gives "all" the UK (landline area) codes from which it can be seen they start with 01xx

https://www.ofcom.org.uk/phones-tel...


Reply ↓  Report •

#19
March 16, 2020 at 04:51:04
Emma1,

Another quick way to reduce you work would be to
Sort your whole sheet on the Telephone Number column.
That should put all the like numbers in groups.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Ask Question