search for a number format

[ermccarthy]

I need to search for a specfic number format, and ignore everything else.

I am looking for a Text format the is 3 numbers, a dash, followed by 8 numbers. (###-########). I know how to write a macro to search for a specific string, but not for a format.

Any Suggestions??

 

[IML]

nevermind, back to drawing board.




the following formula should work (either in a cell for sorting or conditional formatting)
=AND(ISNUMBER(SUBSTITUTE(A1,"-","",1)+0),LEN(SUBSTITUTE(A1,"-","",1)=11),LEN(A1)<>11)

good luck
This message was edited by IML on 2002-04-05 08:36

 

[IML]

Maybe
=AND(LEFT(A1,3)&"-"&RIGHT(A1,8)=A1,LEN(A1)=12,ISNUMBER(SUBSTITUTE(A1,"-","",1)+0))

 

[Mark W.]

On 2002-04-05 08:24, ermccarthy wrote:
I need to search for a specfic number format, and ignore everything else.

I am looking for a Text format the is 3 numbers, a dash, followed by 8 numbers. (###-########). I know how to write a macro to search for a specific string, but not for a format.

Any Suggestions??

If you're just trying to locate where this format has been used on a worksheet, and this is a "one-time" need...

1. Make a copy of your worksheet and select all of its cells.
2. Using ASAP Utilities freeware add-in (see www.asap-utilities.com) choose its Range | Convert cell's value to its formatted look menu command.
3. Now, you can use Excel's Find command and search for ???-????????.
This message was edited by Mark W. on 2002-04-05 08:57