Count cells that contain both numbers & text in same cell

**cnjstewart** · 07-24-2012, 10:22 AM

I have data that I an trying to sort through. All cells have text but some have a number after the text in the same cell. I need to count the number of cells (or rows) that contain a number after the text. For example, this would be the contents of Column 1:

FirstItem
FirssstItem
FirstItem123
Firstttt794
FirstttItem
FirrrstItem806
FFirst
FirrsttIte

Out of these I need a formula to find that there are 3 cells (or rows) that contain numbers. All the solutions that I can find will only tell me if the cell contains numbers or text by itself. And I can't search for individual characters because the numbers and not consistent in any way. Any ideas would help. Thanks.

-C

**Ace_XL** · 07-24-2012, 10:28 AM

In a helper column, use the below

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

this will return blanks if there are no numbers in your list, else it'll separate the number from the alphanumeric text.

After that the COUNT function on this helper column will do the trick!

**cnjstewart** · 07-24-2012, 10:48 AM

That's perfect, works great!! Thank you!

-C

**Cutter** · 07-24-2012, 12:58 PM

@ cnjstewart

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.