Test for telephone or SSN format

[JustATheory]

Greetings,

I need a piece of code that will test for a format such as telephone number 999-999-9999 or SSN 999-99-9999 if one of these standard formats test for false then identify it by placing an "X" in the cell next to it and then continuing on until a null cell.


Any help is greatly appreciated.

Thanks,
Andy

 

[SkipVought]

Hi,

Check out the NumberFormat property of the range object.

Skip,

_[red][/red]

 

[djj55]

In Excel you could use (shown as worksheet formula not VBA):
For telephone number
=IF(MID(A14,4,1)<>"-", "X", IF(MID(A14,8,1)<> "-","X",""))

djj

 

[SkipVought]

Just to be CLEAR...

there's a DIFFERENCE in a STRING like...
[tt]
(999) 123-4567
[/tt]
wher you can do as djj explained

and a NUMBER...
[tt]
9991234567
[/tt]
that is FORMATTED to DISPLAY
[tt]
(999) 123-4567
[/tt]
which you must test as I explained.

You asked to "...test for a format such as ..."

A FORMAT is different than a STRING that is formatted such and such.



Skip,

_[red][/red]

 

[JustATheory]

Hi Skip,

My bad, I did mean a STRING. Basically, we sometimes receive data that is not a phone number or SSN, so I'm looking for anything that is not a phone number or SSN, usually text.

Thanks for clarifying that.

Andy

 

[SkipVought]

Part Numbers, Phone Numbers, SS Numbers, Invoice Numbers, Zip Codes etc...

are ALL

IDENTIFIERS

that are TEXT, and may or may not contain numeric CHARACTERS.

NUMBERS get used in arithmetic operations.

IDENTIFIERS do not.

The are totally different and identifiers should be stored as TEXT -- never as NUMBERS.

Skip,

_[red][/red]