Need formula to Identify Cells that contain both Numbers and Text

[Steve]

Hi,

I have a spreadsheet that has some cells in a column containing both
text and numbers, i.e. a phone number, city and state. In that same
column I have cells that only contain a city and state. I need to come
up with a formula that will identify which cells do not contain
numbers in them.


Please advise,

Steve

 

[Nick Hodge]

Steve

If the number is always first then in a column near it (We'll say the
number/text is in column A enter)

=IF(ISNUMBER(VALUE(LEFT(A1,1))),"Number","Text")

Some may be overkill but I add VALUE to make sure it tries to parse the
first character to a number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[RagDyeR]

And if the number is *not* the first character in the cell, you might try
this:

=IF(ISERROR(LOOKUP(99^99,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))),"NO
Num","Number")
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Steve

If the number is always first then in a column near it (We'll say the
number/text is in column A enter)

=IF(ISNUMBER(VALUE(LEFT(A1,1))),"Number","Text")

Some may be overkill but I add VALUE to make sure it tries to parse the
first character to a number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Steve]

Nick - This last formula took care of things perfectly....Thanks,
Steve

 

[RagDyeR]

Well ... you almost got it right ... the name is Rick.

But ... how would you know that?<bg>

Anyway, appreciate the feed-back.