Highlight if only one word?

[jeffcoleky]

This is probably very simple but i cannot figure it out... I'm trying to highlight any word in Column B that only has numbers... Details below.

Excel 2010

	A	B	C
Address	Short Address
123 Count St	123 Count
555 Anony Mouse Ln	555 Anony Mouse
222 Two St	222 Two
123 Count	<-- Need short addresses containing no street name to be highlighted
555 Anony Mouse	555 Anony
222 Two

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #FFFF00, align: right"]123[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #FFFF00, align: right"]222[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Column A is raw data containing street addresses. Column B shows the same street address adjacent to it, but minus the last word. Here is the formula I use to generate column 2:

=PROPER(LEFT($A2,FIND("?",SUBSTITUTE($A2," ","?",LEN(TRIM($A2))-LEN(SUBSTITUTE($A2," ",""))-CHOOSE(--ISNUMBER(--RIGHT($A2,5))+1,0,1)))-1))

I need to create a conditional formatting rule that will highlight any cell in column B that does not contain a street name, or in other words, only contains numbers. You could also say any cell that does not include text. Or you could do any cell that only has one word in it.

I have tried ISNUMBER, ISTEXT and both don't work in my case because of the way i'm creating the value. The web showed me one formula to determine if the cell only contains one word, and that formula is: =LEN(TRIM(A2))-LEN(SUBSITUTE(A2," ",""))+1 However, that gives me a #NAME? error.

Can anyone please help me figure this out? It seems there should be a short answer to this that would work but I cannot figure it out

 

[West Man]

to check for a number try =isnumber(b5+0)

 

[rohith554]

Use =isnumber(A1) in the conditional formatting

Here, it tests if the cell A1 is a number

Hope this works!

 

[ghrain22]

Try this

=ISERROR(SEARCH(" ",TRIM(A1)))

 

[jeffcoleky]

to check for a number try =isnumber(b5+0)

Yay! an easy solution, thanks! it worked great

 

[CharlesChuckieCharles]

=IF(ISERR(SEARCH(" ",(PROPER(LEFT($A2,FIND("?",SUBSTITUTE($A2," ","?",LEN(TRIM($A2))-LEN(SUBSTITUTE($A2," ",""))-CHOOSE(--ISNUMBER(--RIGHT($A2,5))+1,0,1)))-1))))),$A2,PROPER(LEFT($A2,FIND("?",SUBSTITUTE($A2," ","?",LEN(TRIM($A2))-LEN(SUBSTITUTE($A2," ",""))-CHOOSE(--ISNUMBER(--RIGHT($A2,5))+1,0,1)))-1)))

This makes sure you don't truncate two word cells in the first place

 

[jeffcoleky]

=IF(ISERR(SEARCH(" ",(PROPER(LEFT($A2,FIND("?",SUBSTITUTE($A2," ","?",LEN(TRIM($A2))-LEN(SUBSTITUTE($A2," ",""))-CHOOSE(--ISNUMBER(--RIGHT($A2,5))+1,0,1)))-1))))),$A2,PROPER(LEFT($A2,FIND("?",SUBSTITUTE($A2," ","?",LEN(TRIM($A2))-LEN(SUBSTITUTE($A2," ",""))-CHOOSE(--ISNUMBER(--RIGHT($A2,5))+1,0,1)))-1)))

This makes sure you don't truncate two word cells in the first place

that's nice too thanks

 

[Rick Rothstein]

One the off chance Column B could contain as single word (original address had no number) or a number, you could use this Conditional Formatting formula...

=AND(B1<>"",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))=0)