opposite of IsNumeric
 

Hi,

I know that IsNumeric returns true if the selection is only numbers,
but is there a function that returns true if the selection is all text?
Using If Not IsNumeric does not work, because if something contains text
and numbers it returns true, which is what I do not want.

Thanks,

Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381736

written a function only_text, which check whether a value is all text or
not



Sub macro()
Dim ch_text As Variant
ch_text = InputBox("enter value to test")
If only_text(ch_text) Then
MsgBox "no numeric value found/ all text values"
Else
If IsNumeric(ch_text) Then
MsgBox "numeric value"
Else
MsgBox "has numeric and text value"
End If
End If
End Sub



Function only_text(v As Variant)
Dim p_text As Integer
p_text = 1

For i = 1 To Len(v)
If IsNumeric(Mid(v, i, i + 1)) Then
p_text = 0
End If

Next
If p_text = 0 Then
only_text = False
Else
only_text = True
End If
End Function


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381736

=istext("123xd")
returns true
=isnumber("123xd") return
false

I am not sure of isnumeric function unless its a UDF, which is why it reyrns
true for part text and part number


"thephoenix12"
wrote in message
news:thephoenix12.1r3i6w_1119557148.1824@excelforu m-nospam.com...

Hi,

I know that IsNumeric returns true if the selection is only numbers,
but is there a function that returns true if the selection is all text?
Using If Not IsNumeric does not work, because if something contains text
and numbers it returns true, which is what I do not want.

Thanks,

Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile:
http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381736

Something like:

Option Explicit
Function IsAllText(str As String) As Boolean
Dim iCtr As Long
IsAllText = True
For iCtr = 1 To Len(str)
If UCase(Mid(str, iCtr, 1)) Like "[A-Z]" Then
'ok
Else
IsAllText = False
Exit For
End If
Next iCtr
End Function



thephoenix12 wrote:

Hi,

I know that IsNumeric returns true if the selection is only numbers,
but is there a function that returns true if the selection is all text?
Using If Not IsNumeric does not work, because if something contains text
and numbers it returns true, which is what I do not want.

Thanks,

Steve

--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381736

--

Dave Peterson

Anilsolipuram,

I tried what you said, except, where do I put the function that you
wrote? If I put it after the sub, then an error message appears.


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381736

Never mind I got it...so whenever I create a function, do I have to
create it in each new module I write, or can I use it throughout the
whole workbook and have it defined in just one of my modules?


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381736

Just paste at one place in any module, you need not define it in all
modules, you can access from any module

The good thing about function is that it can called from excel cell

like in a2 cell, you type in

=only_text("trrr000rrrr9")

it will return true in the cell if it only text or else false .


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381736

I am trying to write a simple code like this:


FUNCTION ONLY_TEXT(V AS VARIANT)
DIM P_TEXT AS INTEGER
P_TEXT = 1

FOR I = 1 TO LEN(V)
IF ISNUMERIC(MID(V, I, I + 1)) THEN
P_TEXT = 0
END IF

NEXT
IF P_TEXT = 0 THEN
ONLY_TEXT = FALSE
ELSE
ONLY_TEXT = TRUE
END IF
END FUNCTION

SUB TEXTING()
DIM BLAH AS RANGE
SET BLAH = RANGE(\"A1:A5\")
IF ONLY_TEXT(BLAH) THEN
MSGBOX (\"IT IS ONLY TEXT\")
ELSE
MSGBOX (\"IT CONTAINS SOMETHING ELSE OTHER THAN TEXT\")
END IF
END SUB

but it is not working...it gives me an error at the i part of the
function; any ideas?


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381736

Try this


Function only_text(R As Range)
Dim p_text As Integer
Dim v As Variant
Dim C As Range
p_text = 1
For Each C In R
v = C.Value

For i = 1 To Len(v)

If IsNumeric(Mid(v, i, 1)) Then
p_text = 0
End If
Next
Next
If p_text = 0 Then
only_text = False
Else
only_text = True
End If
End Function

Sub Texting()
Dim Blah As Range
Set Blah = Range("A1:A5")
If only_text(Blah) Then
MsgBox ("it is only text")
Else
MsgBox ("it contains something else other than text")
End If
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381736

That works...awesome...if you get a chance can you explain this:
Function only_text(R As Range) (why R As Range is there)
and this: If IsNumeric(Mid(v, i, 1)) Then

Thanks,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381736

only_text(R As Range)

you are passing the range to the function only_text, so only_text
function has the parameter range , it can be any name (R in this case),
this R is used in the function to retrieve individual values in the
range by using for each c in R

V=c.value , will retrive the value in single cell in Range R,
mid(v,1,1) will retieve content in variable v first leter
mid(v,2,1)will retieve content in variable v second leter
mid(v,3,1)will retieve content in variable v third leter