Page 1 of 3 123 LastLast
Results 1 to 10 of 21

VBA to see If cell value is text/number

This is a discussion on VBA to see If cell value is text/number within the Excel Questions forums, part of the Question Forums category; Hello All, Is there a VBA function which can check to see if a cell is a string or number? ...

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Posts
    916

    Default VBA to see If cell value is text/number

    Hello All,

    Is there a VBA function which can check to see if a cell is a string or number?

    I.e say had dates and numbers in A1:A10

    Although some might look the same, they might be numbers and strings.

    Is there a way i can check to see what they are (Numbers/Strings).

    Is there also a quick VBA way to convert them all to a string or Number depending on which one i want?

    Cheers

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA to see If cell value is text/number

    You can check for numbers using IsNumeric.
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Nov 2011
    Posts
    916

    Default Re: VBA to see If cell value is text/number

    Quote Originally Posted by Norie View Post
    You can check for numbers using IsNumeric.
    Thank You

    I tried this and it gave me a 7 or 8. I believe 7 might be a date and 8 a string. Is there a way i can display it as Date rather 7 and String rather than 8?

    Code:
    Sub test()
     
    For Each cell In Sheets("sheet1").Range("A1:A10")
        MsgBox VarType(cell)
    Next
     
    End Sub
    Is there a difference between VarType and Typename?
    Last edited by Siyanna; Apr 15th, 2012 at 10:58 AM.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA to see If cell value is text/number

    Try TypeName.

    By the way to check for dates you can use IsDate.

    Oh, almost forgot, if the value us text but can be recognised as a date then IsDate will return True.

    The same applies for IsNumber.
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Nov 2011
    Posts
    916

    Default Re: VBA to see If cell value is text/number

    Quote Originally Posted by Norie View Post
    Try TypeName.

    By the way to check for dates you can use IsDate.

    Oh, almost forgot, if the value us text but can be recognised as a date then IsDate will return True.

    The same applies for IsNumber.
    Thank you.

    Now that might be a problem (the value is text but can be recognised as a date then IsDate will return True)

    I have some dates that look like dates but are actually strings.

    I need to check to see if the value is a string/date/number etc?

    If it is a String, is there a quick way to change string values to Date and vice versa?

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA to see If cell value is text/number

    Is there a problem with having 'text' dates?

    What is it you are trying to do?
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Nov 2011
    Posts
    916

    Default Re: VBA to see If cell value is text/number

    Quote Originally Posted by Norie View Post
    Is there a problem with having 'text' dates?

    What is it you are trying to do?

    Hi, my aim is to convert every dates that entered as text to actual dates
    Any numbers that have been entered as text as numbers

    Hope this makes sense. I want a vba solution to do this.

    I guess i wanted to see the values which were extracted incorrectly and wanted to see the type but i guess thats not required.

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA to see If cell value is text/number

    Just add 0 to all the values.

    If they are text that will convert it to a number/date and if they are already number/date they won't be changed.

    You would want to avoid blank cells though as if you add 0 to a blank the result is 0.
    If posting code please use code tags.

  9. #9
    Board Regular
    Join Date
    Nov 2011
    Posts
    916

    Default Re: VBA to see If cell value is text/number

    I think it might be something to do with text to columns ( I need it in VBA).

    I have recorded via macro recorder and the it works fine however....

    I am not sure about these 4 lines of the code

    TextQualifier:=xlDoubleQuote, _ what is the DoubleQuote text qualifier?
    ConsecutiveDelimiter:=False, _ ???
    FieldInfo:=Array(1, 3), _ ???
    TrailingMinusNumbers:=True ???

    I have tried to read through the help but still not getting it

    I am not sure how to perform this for All my data rather than just the first column. I have 8 columns of data
    Last edited by Siyanna; Apr 15th, 2012 at 12:25 PM.

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA to see If cell value is text/number

    Add 0 to all the values using PasteSpecial>Operations add which you can do using code.
    Code:
    Cells(1, Columns.Count).Copy
     
    Range("A1:H1000").PasteSpecial Operation:=xlPasteSpecialOperationAdd
    If posting code please use code tags.

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com