Extract Data from a Phone Number

[cynteeuh]

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

 

[Ofer]

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),"-","")

 

[cynteeuh]

THANK YOU!!!

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),"-","")

 

[cynteeuh]

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!

 

[James Arnold]

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

 

[Ofer]

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]

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!

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!

:

 

[cynteeuh]

That's it, thank you, thank you!

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!

 

[Ofer]

Any time

That's it, thank you, thank you!

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

 

[cynteeuh]

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)

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!

 

[Ofer]

Try that

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

That's it, thank you, thank you!

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

 

[Ofer]

Try that

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

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)

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

 

[cynteeuh]

Works beautiful! Thank you again!

Try that

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

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)

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