Cell contains number or just text

[lovinguy]

Hi,

I have a column which contains data which is in number,text and combination of number+text. I want to apply a formula to find the cells which contains only text.

Tried to use ISTEXT and ISNUMBER but not working.

Attached sheet contains example.Desired output is highlighted with yellow color

Thanks

[Ron Coderre]

Using your posted workbook, this regular formula returns TRUE for cells containing no digits, otherwise FALSE:

B1: =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))>LEN(A1)

or...for a descriptive result:

B1: =IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))>LEN(A1),"TEXT","CONTAINS NUMBER")

Is that something you can work with?

[FDibbins]

those cells that contain numbers are still text, because they also contain text

Try this, copied down...
=IF(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))=0,"TEXT","Contains Number")
(base formula Curtesy of:*Ron Coderre)

edit: LOL Ron

[martindwilson]

Another
=IF(ISERROR(LOOKUP(10^99,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),"TEXT","CONTAINS NUMBER")
or shorter
=IF(ISERROR(LOOKUP(10^99,--MID(A1,ROW($1:$255),1))),"TEXT","CONTAINS NUMBER")

[lovinguy]

Possible to get values pasted on 3rd column if the value is "TEXT"

Thanks in advance

[FDibbins]

Not exactly sure what you mean there, but this will either give a 0 if there no numbers, or the number within the text string...

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))...Curtesy of:*Ron Coderre