VBA to see If cell value is text/number

[Siyanna]

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

 

[Norie]

You can check for numbers using IsNumeric.

 

[Siyanna]

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?

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?

 

[Norie]

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.

 

[Siyanna]

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?

 

[Norie]

Is there a problem with having 'text' dates?

What is it you are trying to do?

 

[Siyanna]

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.

 

[Norie]

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.

 

[Siyanna]

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

 

[Norie]

Add 0 to all the values using PasteSpecial>Operations add which you can do using code.

Cells(1, Columns.Count).Copy
 
Range("A1:H1000").PasteSpecial Operation:=xlPasteSpecialOperationAdd