opposite of IsNumeric

  • Thread starter Thread starter thephoenix12
  • Start date Start date
T

thephoenix12

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
 
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
 
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
 
Anilsolipuram,

I tried what you said, except, where do I put the function that yo
wrote? If I put it after the sub, then an error message appears
 
Never mind I got it...so whenever I create a function, do I have t
create it in each new module I write, or can I use it throughout th
whole workbook and have it defined in just one of my modules
 
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 .
 
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?
 
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
 
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
 
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
 
Back
Top