opposite of IsNumeric

[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

[anilsolipuram]

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

[N Harkawat]

=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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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]

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

[thephoenix12]

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]

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?

[anilsolipuram]

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 .

[thephoenix12]

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?

[anilsolipuram]

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

[thephoenix12]

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

[anilsolipuram]

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
.
.
.
so it is mid(v,i,1) , if we keep in the loop till whole length of variable v, which is len(v).

isnumeric( , as you know tells whether number or not