Extract Data from a Phone Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a phone number that I'd like to extract the last five digits, but when
I use the "right" function, I get the "-". The numbers are stored without
symbols and I'm using the phone number input mask.

Is there a way to extract the last five digits without the "-" or how do I
change my field so there aren't any symbols even when viewing the data in
datasheet view?

(102) 123-5467 Current
35467 Goal
 
If the fields always has the dash, and there are four digits after the dash,
the you can use the right combined with replace for the dash

replace(right(MyField ,6),"-","")
 
THANK YOU!!!

Ofer said:
If the fields always has the dash, and there are four digits after the dash,
the you can use the right combined with replace for the dash

replace(right(MyField ,6),"-","")
Click to expand...
 
What if I had an exception. For example if a field equaled a certain value
then the number would be 1+last four digits? Basically, I'm storing phone
numbers for a company that has mulitple locations. For all locations, the
extension is the last five numbers, with the exception of one location, which
I'd like to incorporate in the formula below ( replace(right(MyField
,6),"-","").

For example: 604 452 3748
Location=Canada, then the number is 13748 or change the "2" to "1"

Thanks for any help!
 
If the number is always 604 452 3748 you could use...

If [MyField]="604 452 3748" Then
MyString="13748"
Else
Replace(Right(MyField ,6),"-","")
End if
 
Then if using in a query or source of a field t can be
IIF([Location]="Canada", "1" & right(MyField,4), replace(right(MyField
,6),"-",""))
 
The constant is 604-456-xxxx. Instead of it being the last five numbers, I
need "1" plus the last four numbers (e.g. 604-452-4564 would be 14564 or
604-452-6828 would be 16828).

For the MyString portion, would I type: ... Then
MyString="1"&Right([Myfield],4)?

Thanks again!

James Arnold said:
If the number is always 604 452 3748 you could use...

If [MyField]="604 452 3748" Then
MyString="13748"
Else
Replace(Right(MyField ,6),"-","")
End if
What if I had an exception. For example if a field equaled a certain value
then the number would be 1+last four digits? Basically, I'm storing phone
numbers for a company that has mulitple locations. For all locations, the
extension is the last five numbers, with the exception of one location, which
I'd like to incorporate in the formula below ( replace(right(MyField
,6),"-","").

For example: 604 452 3748
Location=Canada, then the number is 13748 or change the "2" to "1"

Thanks for any help!

:
Click to expand...
Click to expand...
 
That's it, thank you, thank you!

Ofer said:
Then if using in a query or source of a field t can be
IIF([Location]="Canada", "1" & right(MyField,4), replace(right(MyField
,6),"-",""))


cynteeuh said:
What if I had an exception. For example if a field equaled a certain value
then the number would be 1+last four digits? Basically, I'm storing phone
numbers for a company that has mulitple locations. For all locations, the
extension is the last five numbers, with the exception of one location, which
I'd like to incorporate in the formula below ( replace(right(MyField
,6),"-","").

For example: 604 452 3748
Location=Canada, then the number is 13748 or change the "2" to "1"

Thanks for any help!
Click to expand...
Click to expand...
 
Any time :-)

cynteeuh said:
That's it, thank you, thank you!

Ofer said:
Then if using in a query or source of a field t can be
IIF([Location]="Canada", "1" & right(MyField,4), replace(right(MyField
,6),"-",""))


cynteeuh said:
What if I had an exception. For example if a field equaled a certain value
then the number would be 1+last four digits? Basically, I'm storing phone
numbers for a company that has mulitple locations. For all locations, the
extension is the last five numbers, with the exception of one location, which
I'd like to incorporate in the formula below ( replace(right(MyField
,6),"-","").

For example: 604 452 3748
Location=Canada, then the number is 13748 or change the "2" to "1"

Thanks for any help!

:

If the fields always has the dash, and there are four digits after the dash,
the you can use the right combined with replace for the dash

replace(right(MyField ,6),"-","")

:

I have a phone number that I'd like to extract the last five digits, but when
I use the "right" function, I get the "-". The numbers are stored without
symbols and I'm using the phone number input mask.

Is there a way to extract the last five digits without the "-" or how do I
change my field so there aren't any symbols even when viewing the data in
datasheet view?

(102) 123-5467 Current
35467 Goal
Click to expand...
Click to expand...
Click to expand...
 
Last question (I hope): How would I embed one more "IIf" statement in the
below? I just realized there's one more exception (two altogether).

How would I embed this statement into the below one? IIF([Location]="UK",
right([MyField],5)

Ofer said:
Then if using in a query or source of a field t can be
IIF([Location]="Canada", "1" & right(MyField,4), replace(right(MyField
,6),"-",""))


cynteeuh said:
What if I had an exception. For example if a field equaled a certain value
then the number would be 1+last four digits? Basically, I'm storing phone
numbers for a company that has mulitple locations. For all locations, the
extension is the last five numbers, with the exception of one location, which
I'd like to incorporate in the formula below ( replace(right(MyField
,6),"-","").

For example: 604 452 3748
Location=Canada, then the number is 13748 or change the "2" to "1"

Thanks for any help!
Click to expand...
Click to expand...
 
Try that

IIF([Location]="Canada", "1" & right(MyField,4),IIF([Location]="UK",
right([MyField],5), replace(right(MyField
,6),"-","")) )

cynteeuh said:
That's it, thank you, thank you!

Ofer said:
Then if using in a query or source of a field t can be
IIF([Location]="Canada", "1" & right(MyField,4), replace(right(MyField
,6),"-",""))


cynteeuh said:
What if I had an exception. For example if a field equaled a certain value
then the number would be 1+last four digits? Basically, I'm storing phone
numbers for a company that has mulitple locations. For all locations, the
extension is the last five numbers, with the exception of one location, which
I'd like to incorporate in the formula below ( replace(right(MyField
,6),"-","").

For example: 604 452 3748
Location=Canada, then the number is 13748 or change the "2" to "1"

Thanks for any help!

:

If the fields always has the dash, and there are four digits after the dash,
the you can use the right combined with replace for the dash

replace(right(MyField ,6),"-","")

:

I have a phone number that I'd like to extract the last five digits, but when
I use the "right" function, I get the "-". The numbers are stored without
symbols and I'm using the phone number input mask.

Is there a way to extract the last five digits without the "-" or how do I
change my field so there aren't any symbols even when viewing the data in
datasheet view?

(102) 123-5467 Current
35467 Goal
Click to expand...
Click to expand...
Click to expand...
 
Try that

IIF([Location]="Canada", "1" & right(MyField,4),IIF([Location]="UK",
right([MyField],5), replace(right(MyField
,6),"-","")) )



cynteeuh said:
Last question (I hope): How would I embed one more "IIf" statement in the
below? I just realized there's one more exception (two altogether).

How would I embed this statement into the below one? IIF([Location]="UK",
right([MyField],5)

Ofer said:
Then if using in a query or source of a field t can be
IIF([Location]="Canada", "1" & right(MyField,4), replace(right(MyField
,6),"-",""))


cynteeuh said:
What if I had an exception. For example if a field equaled a certain value
then the number would be 1+last four digits? Basically, I'm storing phone
numbers for a company that has mulitple locations. For all locations, the
extension is the last five numbers, with the exception of one location, which
I'd like to incorporate in the formula below ( replace(right(MyField
,6),"-","").

For example: 604 452 3748
Location=Canada, then the number is 13748 or change the "2" to "1"

Thanks for any help!

:

If the fields always has the dash, and there are four digits after the dash,
the you can use the right combined with replace for the dash

replace(right(MyField ,6),"-","")

:

I have a phone number that I'd like to extract the last five digits, but when
I use the "right" function, I get the "-". The numbers are stored without
symbols and I'm using the phone number input mask.

Is there a way to extract the last five digits without the "-" or how do I
change my field so there aren't any symbols even when viewing the data in
datasheet view?

(102) 123-5467 Current
35467 Goal
Click to expand...
Click to expand...
Click to expand...
 
Works beautiful! Thank you again!

Ofer said:
Try that

IIF([Location]="Canada", "1" & right(MyField,4),IIF([Location]="UK",
right([MyField],5), replace(right(MyField
,6),"-","")) )



cynteeuh said:
Last question (I hope): How would I embed one more "IIf" statement in the
below? I just realized there's one more exception (two altogether).

How would I embed this statement into the below one? IIF([Location]="UK",
right([MyField],5)

Ofer said:
Then if using in a query or source of a field t can be
IIF([Location]="Canada", "1" & right(MyField,4), replace(right(MyField
,6),"-",""))


:

What if I had an exception. For example if a field equaled a certain value
then the number would be 1+last four digits? Basically, I'm storing phone
numbers for a company that has mulitple locations. For all locations, the
extension is the last five numbers, with the exception of one location, which
I'd like to incorporate in the formula below ( replace(right(MyField
,6),"-","").

For example: 604 452 3748
Location=Canada, then the number is 13748 or change the "2" to "1"

Thanks for any help!

:

If the fields always has the dash, and there are four digits after the dash,
the you can use the right combined with replace for the dash

replace(right(MyField ,6),"-","")

:

I have a phone number that I'd like to extract the last five digits, but when
I use the "right" function, I get the "-". The numbers are stored without
symbols and I'm using the phone number input mask.

Is there a way to extract the last five digits without the "-" or how do I
change my field so there aren't any symbols even when viewing the data in
datasheet view?

(102) 123-5467 Current
35467 Goal
Click to expand...
Click to expand...
Click to expand...
 
Back
Top