If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Does the cell have a number?
I want a formula that will tell me if a given cell has a number in it, so it
evaluates the type of data. I looked in the help files and couldn't figure this out. Thanks, Craig |
#2
|
|||
|
|||
=ISNUMBER(a1)
"Fletcher" wrote: I want a formula that will tell me if a given cell has a number in it, so it evaluates the type of data. I looked in the help files and couldn't figure this out. Thanks, Craig |
#3
|
|||
|
|||
I tried that, but if the cell has numbers and text, it replies back with
FALSE. I would like to be able to determine if any part of the cell contents is a number, even if the string starts with, or contains letters. Thanks! "LanceB" wrote in message ... =ISNUMBER(a1) "Fletcher" wrote: I want a formula that will tell me if a given cell has a number in it, so it evaluates the type of data. I looked in the help files and couldn't figure this out. Thanks, Craig |
#4
|
|||
|
|||
Fletcher
You could use a UDF Function hasNumbers(myString As String) As Boolean If myString Like "*[1234567890]*" Then hasNumbers = True Else hasNumbers = False End If End Function usage is: =hasNumbers(cellref) Gord Dibben Excel MVP On Tue, 25 Jan 2005 13:15:33 -0700, "Fletcher" wrote: I tried that, but if the cell has numbers and text, it replies back with FALSE. I would like to be able to determine if any part of the cell contents is a number, even if the string starts with, or contains letters. Thanks! "LanceB" wrote in message ... =ISNUMBER(a1) "Fletcher" wrote: I want a formula that will tell me if a given cell has a number in it, so it evaluates the type of data. I looked in the help files and couldn't figure this out. Thanks, Craig |
#5
|
|||
|
|||
=SUMPRODUCT(--(ABS(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) -
52.5)=4.5))0 -- Rob van Gelder - http://www.vangelder.co.nz/excel "Fletcher" wrote in message ... I tried that, but if the cell has numbers and text, it replies back with FALSE. I would like to be able to determine if any part of the cell contents is a number, even if the string starts with, or contains letters. Thanks! "LanceB" wrote in message ... =ISNUMBER(a1) "Fletcher" wrote: I want a formula that will tell me if a given cell has a number in it, so it evaluates the type of data. I looked in the help files and couldn't figure this out. Thanks, Craig |
#6
|
|||
|
|||
Would you mind explaining why this formula works?
Thanks, tj "Rob van Gelder" wrote: =SUMPRODUCT(--(ABS(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) - 52.5)=4.5))0 -- Rob van Gelder - http://www.vangelder.co.nz/excel "Fletcher" wrote in message ... I tried that, but if the cell has numbers and text, it replies back with FALSE. I would like to be able to determine if any part of the cell contents is a number, even if the string starts with, or contains letters. Thanks! "LanceB" wrote in message ... =ISNUMBER(a1) "Fletcher" wrote: I want a formula that will tell me if a given cell has a number in it, so it evaluates the type of data. I looked in the help files and couldn't figure this out. Thanks, Craig |
#7
|
|||
|
|||
MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1) allows the string to be split into
individual entries CODE(..) returns the ascii code for each character - I'm looking for numbers which are in the range 48 to 57 for "0" to "9" Then subtract 52.5, which is midway between 48 and 57 48+(57-48)/2 where (57-48)/2 = 4.5 ABS turns negative numbers to positive. So this means ascii codes for numbers are from 0 to 4.5 Check to see whether any of the numbers are less than or equal to 4.5 which returns a series of TRUE/FALSE -- turns TRUE, FALSE to 1, 0 SUMPRODUCT adds the entries of an array. 0 means the count of characters which were identified as numbers -- Rob van Gelder - http://www.vangelder.co.nz/excel "tjtjjtjt" wrote in message ... Would you mind explaining why this formula works? Thanks, tj "Rob van Gelder" wrote: =SUMPRODUCT(--(ABS(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) - 52.5)=4.5))0 -- Rob van Gelder - http://www.vangelder.co.nz/excel "Fletcher" wrote in message ... I tried that, but if the cell has numbers and text, it replies back with FALSE. I would like to be able to determine if any part of the cell contents is a number, even if the string starts with, or contains letters. Thanks! "LanceB" wrote in message ... =ISNUMBER(a1) "Fletcher" wrote: I want a formula that will tell me if a given cell has a number in it, so it evaluates the type of data. I looked in the help files and couldn't figure this out. Thanks, Craig |
#8
|
|||
|
|||
Thank you. It's quite clever.
tj "Rob van Gelder" wrote: MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1) allows the string to be split into individual entries CODE(..) returns the ascii code for each character - I'm looking for numbers which are in the range 48 to 57 for "0" to "9" Then subtract 52.5, which is midway between 48 and 57 48+(57-48)/2 where (57-48)/2 = 4.5 ABS turns negative numbers to positive. So this means ascii codes for numbers are from 0 to 4.5 Check to see whether any of the numbers are less than or equal to 4.5 which returns a series of TRUE/FALSE -- turns TRUE, FALSE to 1, 0 SUMPRODUCT adds the entries of an array. 0 means the count of characters which were identified as numbers -- Rob van Gelder - http://www.vangelder.co.nz/excel "tjtjjtjt" wrote in message ... Would you mind explaining why this formula works? Thanks, tj "Rob van Gelder" wrote: =SUMPRODUCT(--(ABS(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) - 52.5)=4.5))0 -- Rob van Gelder - http://www.vangelder.co.nz/excel "Fletcher" wrote in message ... I tried that, but if the cell has numbers and text, it replies back with FALSE. I would like to be able to determine if any part of the cell contents is a number, even if the string starts with, or contains letters. Thanks! "LanceB" wrote in message ... =ISNUMBER(a1) "Fletcher" wrote: I want a formula that will tell me if a given cell has a number in it, so it evaluates the type of data. I looked in the help files and couldn't figure this out. Thanks, Craig |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I need to increase a number which is held in a cell by 1 | Steve | New Users | 1 | January 14th, 2005 12:07 PM |
GET.CELL | Biff | Worksheet Functions | 2 | November 24th, 2004 07:16 PM |
Cell -find number of columns unitl next non-blank cell. | Joe | Worksheet Functions | 2 | May 28th, 2004 08:50 PM |
Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |