How to select all text items, but not text numbers

[jomili]

My title say it all. Using VBA how do I select all text that's NOT a text number?

[mjr veverka]

What "select all text items" mean for you ?
Are they maybe text strings in cells or e.g. in text fields ?

[jomili]

By text I mean a string like "Bobby" or "100FGB-254" or "IBM" or anything containing a text item.
By numbers vs Text Numbers I mean 1000, not '1000

I'm not sure I know what Text Fields are in Excel.

[mjr veverka]

Is this data in a specific range of sheet cells ?
Can this range (if any) be predetermined (a priori), or will it be unknown ?

[jomili]

It will be whatever range I select at the time, probably a single column, but possibly the entire sheet.

[mjr veverka]

... text ... "Bobby" or "100FGB-254" or "IBM" or anything containing a text item.
By numbers vs Text Numbers I mean 1000, not '1000

This number/text/string/item "100fff" is also "Text Number", this is hexadecimal "1052671"

Attach an example with more data and show what is what.

[arthurbr]

You can highlight numbers or text using the TYPE() function

[jomili]

Arthurbr,
See the attached. Text is Type 2, and TextNumbers are Type 2. So "Type" doesn't differentiate between Text and Text Numbers. In any list of numbers and text numbers I can use the ISNUMBER function to determine which is a Number, but that still don't give me there, because ISNUMBER doesn't differentiate between Text and TextNumbers either.

I have two simple macros below, to select all Text value or to Select all Numeric values. TextNumbers are in a unique class between these two, in some cases they can considered numbers, in some cases they can be considered Text. So my dilemma is, how do I select only the TextNumbers?

Please Login or Register  to view this content.

[jindon]

You mean like this?

Please Login or Register  to view this content.

[jomili]

Jindon,
Your macro selected only the numbers. What I want to select are only the TextNumbers.

[jindon]

Then the other way around

Please Login or Register  to view this content.

[romperstomper]

You could loop and use Excel's error checking:

Please Login or Register  to view this content.

To exclude the text numbers, just change the error test to False instead of True.

[jomili]

Nope that selects all the Text, both Textvalues (words) and TextNumbers. Only want to select TextNumbers.

The only way I can figure to do it is to text each individual cell. If TYPE is text, and cell.value*1 is a number (or doesn't produce an error) then it's a TextNumber. But how could we do this so it would operate on a large range quickly?

[jomili]

Rorya,

I think you're done it! I've never seen that "xlNumberAsText" property before. Where does that come from? And THANK YOU!

[romperstomper]

It's one of Excel's built-in error checking options.

[jomili]

Rorya,
You've just opened a new world for me. I never thought of using the error-checking properties to distinguish special cells/conditions. Thanks for showing me this!