Check first character of string for if a number

[gailb]

I have a string as below. The first character is a number, so I need to proceed with another part of my macro. How can I confirm a number? I tried but have failed with the code below

1. Who created the heavens and the earth?

Code: Select all

Sub dkdfjd()
    Dim ws As Worksheet: Set ws = Sheets("Sheet2")
    Dim LastRow As Long: LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    Dim i As Long
    For i = 4 To LastRow
        If IsNumeric(InStr(1, ws.Range("A" & i))) Then
            MsgBox "Yes"
        End If
    Next i
End Sub

[Leif]

I'm not the best at VBA, but wouldn't you want to use 'Left' rather than 'Instr'?

[gailb]

Hi Leif,

That works great thanks. I tried Left at first and it didn't work. Come to find out, I couldn't leave the 1 off the end even though I was only checking one character. Thanks again.

[HansV]

There is a difference between the Excel worksheet function LEFT and the VBA function Left.
In a formula in a cell, LEFT(A1) is equivalent to LEFT(A1, 1).
But in the VBA function, the second argument is required: Left(ws.Range("A" & i)) causes an error - you must use Left(ws.Range("A" & i), 1) if you want the 1st character.

[snb]

Code: Select all

Sub M_snb()
  sn=cells(1).currentregion

  For j= 4 To Ubound(sn)
    Msgbox format(val(sn(j,1)),"yes/no")
  Next
End Sub

[ChrisGreaves]

1. Who created the heavens and the earth?

37. Never mind the previous 36 questions. Who put all these rocks in the Newfoundland soil?

What Leif said

Code: Select all

If IsNumeric(LEFT(1, ws.Range("A" & i))) Then

Although, to deal with my example above, I would be inclined to parse the string ws.Range("A" & i)) into words and then hunt for any decimal anywhere in the word, which should catch as well examples such as :

Code: Select all

(38) And close the only bank branch in town?

I shall post my numeric-testing VBA code after I have cleared my emails.
Cheers, Chris

[ChrisGreaves]

I shall post my numeric-testing VBA code after I have cleared my emails.

As promised:-

Code: Select all

Public Function strOnly(strIn As String, strRef As String) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''' Function:   strOnly
    '''
    ''' Comments:   Returns only those characters in strIn which can be found in strRef.
    '''
    ''' Arguments:  STRING          String to be examined.
    '''             STRING          Set of characters thought to exist in the first string.
    '''
    ''' Returns:    STRING          Those characters, in sequence, in strIn which belong to strRef
    '''
    ''' Date        Developer       Action
    ''' --------------------------------------------------------------------------
    ''' 2008/11/22  Chris Greaves   Created
    '''
    Dim strOut As String
    strOut = ""
    Dim lngI As Long
    For lngI = 1 To Len(strIn)
        Dim strMid As String
        strMid = Mid(strIn, lngI, 1)
        If InStr(1, strRef, strMid) > 0 Then
            strOut = strOut & strMid
        Else
        End If
    Next lngI
    strOnly = strOut
'Sub TESTstrOnly()
'    Debug.Assert "aabe" = strOnly("alphabet", "abcde") ' regular use "aabe" of 1st string exist in 2nd string
'    Debug.Assert 0 = Len(strOnly("", "abcde")) ' empty source string yields empty result string, length = 0
'    Debug.Assert 0 = Len(strOnly("alphabet", "")) ' empty reference string yields empty result string, length = 0
'    Debug.Assert "alphabetanddigit" = strOnly("alphabetand1digit", "abcdefghijklmnopqrstuvwxyz") ' strip digit "1"
'End Sub
End Function

This is my basic "slave" or low-level function which is used in a variety of other functions.
The reference string can be any string, but some basic reference strings are provided below.
This function is a self-testing function: Drag the commented "Sub Test" lines outside the function and run the macro "TESTstrOnly"; the macro should run with no visible output. If there is a problem then the macro will halt on the appropriate "Debug.Assert" command.


Auxiliary constants

Code: Select all

Public Const strcLowerAlpha As String = "abcdefghijklmnopqrstuvwxyz"
Public Const strcUpperAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Public Const strcAlpha As String = strcLowerAlpha & strcUpperAlpha
Public Const strcDigits As String = "0123456789"
Public Const strcAlphaDigits As String = strcAlpha & strcDigits
'

'''
''' Neman's definition of illegal characters in a path item
'''
Public Const strcTypeableIllegalCharacters As String = "\/:*?""<>|" ' NS:348
Public Const strcTypeableLegalCharacters As String = "`~!@#$%^&()-_+=}]{[';,"
Public Const strcTypeableCharacters As String = " " & "." & strcTypeableIllegalCharacters & strcTypeableLegalCharacters
Public Const strcTLC As String = strcAlphaDigits & strcTypeableLegalCharacters
'''

Cheers, Chris

[snb]

@chris

did you have a look at
https://eileenslounge.com/viewtopic.php ... 69#p313669

[ChrisGreaves]

@chris did you have a look at https://eileenslounge.com/viewtopic.php ... 69#p313669

Hi snb; yes, I had seen your post but
(1) I had earlier this day promised to post my code and
(2) The code despite being labelled 2008 was written in 1997/98 and so has been working well in my utility library UW.DOT for a quarter of a century and
(3) I thought that the idea of self-testing functions was worth promoting for anyone considering programming as a career!
Cheers, Chris

[snb]

@chris

But I don't think the code you posted will answer the OP's question.

Code: Select all

Sub M_snb()
   Msgbox "4hjdke" Like "#*"
   Msgbox "ghjsd" Like "#*"
End Sub

[Toranaga]

Code: Select all

Sub M_snb()
  sn=cells(1).currentregion

  For j= 4 To Ubound(sn)
    Msgbox format(val(sn(j,1)),"yes/no")
  Next
End Sub

If string start with 0 (zero) your code rezult (is not good), is not "yes" is "no"

[snb]

@Toranaga

As you can see in https://eileenslounge.com/viewtopic.php ... 56#p313656
that is not the case. It starts with 1.

[Toranaga]

@Toranaga

As you can see in https://eileenslounge.com/viewtopic.php ... 56#p313656
that is not the case. It starts with 1.

@snb
OP say...

I have a string as below. The first character is a number.....