VBA to see If cell value is text/number

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You can check for numbers using IsNumeric.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Is there a problem with having 'text' dates?

What is it you are trying to do?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top