text or number

[mrinal saha]

Hi Folks,

I want to know both in spreadsheet and vba to find whether the first four character in a cell is numeric or text. Both the ways.

I am using this but this doesn't work

If Application.WorksheetFunction.IsText(Left(cel).Value, 3) Then
cel.Value = " "

Thanks,
Mrinal

 

[njimack]

Formula approach: (returns FALSE for numerical, TRUE for text)
=ISERROR(LEFT(A1,4)+0)

VBA:

Sub istext()
On Error GoTo errHandler
If Not IsError(Left(Range("A1"), 4) + 0) Then
MsgBox "Numerical"
Exit Sub
End If
 
errHandler: MsgBox "Text"
End Sub

 

[Joe4]

Likewise, on a spreadsheet, you can use:
=ISNUMBER(LEFT(A2,4)+0)
If it returns TRUE it is a number.
If it returns FALSE then it is text.

(I believe you asked for both VBA abd spreadsheet solutions).

 

[mrinal saha]

just want to know is this wrong,,because line in red is not responding. Can't we use left with (cells(

For xx = 5 To ActiveSheet.Range("q10000").End(xlUp).Row

If Not IsError(Left(Cells(xx, 17), 4) + 0) Then
Cells(xx, 17).Value = " "
End If
Next xx

thanks,
Mrinal

 

[Joe4]

What is the value in the cell it is looking at when the problem occurs?

It might help to know what possible values you may have in the cells you are working with (i.e. are blanks possible, spaces, special characters, etc).

 

[mrinal saha]

this is the column its looking

<TABLE style="WIDTH: 116pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=155 border=0><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 116pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=155 height=20>tors, L.P.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2005 SMBIP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>r, David R</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Michael W.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>kat, Nadim</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ar, Vikram</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Joshua S.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>, David J.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Corp. III</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>orporation</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ement, LLC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Daniel N.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>oan McKown</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>rles Scott</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Michael S.</TD></TR></TBODY></TABLE>

thnx
Mrinal

 

[Joe4]

The problem is because Neil's VBA code he posted has Error Handling code in it to handle the errors, and your's does not.

What is the ultimate goal here?
Is it to just blank out all entries that do not start with 4 numbers?

 

[mrinal saha]

yes , if starting 4 characters from left of cell are not numerice, make it blank.

 

[Joe4]

Try this:

    For xx = 5 To ActiveSheet.Range("q10000").End(xlUp).Row
        If Not IsNumeric(Left(Cells(xx, 17), 4)) Then Cells(xx, 17) = ""
    Next xx

 

[mrinal saha]

thanks a lot...its working now

thanks,
Mrinal