Exclude bad data to get area code

[OPJayhawk]

My overall goal is to have a map showing where calls are coming from by area code. I'm torn on what to do with the bad data....default it to the most common area code (913), leave it blank, mark it undefined in some way. I'm open to feedback there.

Now, on to the data. I want a separate column that just has the area code of the phone number. The most common form of the phone number is 11 digits long starting with 1 followed by the 3 digit area code (ex: 15672347890). I can easily get that.

Here are my exceptions (numbers changed to 2345678 where privacy is a concern). Should I do a nested IF statement to address all of these? What would that look like if we defaulted all unobtainable area codes to '913'?

+2542345678
+8132345678
10000000000

Unavailable

110000000

162017

1.19131E12
1.44291E12

+12012345678

919442345678

+114022345678

15712345678;verstat=TN
anonymous@199.199.199.199
NONE@199.199.199.199
+1217234567890

[OPJayhawk · Accepted solution]

Here's what I ended up using. It doesn't address all of my exceptions but gets me in the ballpark. Specifically, I'm excluding any numbers under 10 digits, any non numeric values and any result that starts with a 1.

CallingAreaCode = 
Var ACode=left(right(Call_Log[CallingNumber],10),3)
Return
    IF(iserror(value(ACode)),"UNK"
,if(value(Acode)<200,"UNK"
,if(len(Call_Log[CallingNumber])<=9, "UNK"
,ACode)))

The values on here that have an area code but that I'm not capturing (or capturing incorrectly) are:

1.19131E12

1.44291E12

15712345678;verstat=TN

+1217234567890

I could probably write a few more IF statements to look at the first 5 or so characters and grab the remaining exceptions but that seems like more trouble than it's worth.

[v-yangliu-msft]

Hi  @OPJayhawk ,

Here are the steps you can follow：

1. Create calculated column.

Flag1 =
IF(
    LEN('Table'[Flag])=11&&MID('Table'[Flag],1,1)="1",RIGHT('Table'[Flag],3),BLANK())

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[OPJayhawk]

Thanks for the reply and the attachment.