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
 
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


Thank You. I will try your method

Based on the text to columns method i tried this to go through all my columns. Is there any chance you can advise why it is not looping through all my columns?

Code:
Sub Test()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
 
Dim Lcol As Long
Lcol = Sheets("sheet1").Range("A" & Columns.Count).End(xlToLeft).Column
 
 For Each col In ws.Range("A" & Lcol).Columns
        Columns(col.Column).TextToColumns _
        Destination:=Cells(1, col.Column), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(1, 4), _
        TrailingMinusNumbers:=True
 Next
End Sub
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You are only looping through one cell/column, ALcol, eg if Lcol = 10, A10.

Try This.

For I = 1 To LCol

Columns(I).Columns(col.Column).TextToColumns _
Destination:=Cells(1, I), _
...
Next I
[/code]
 
Upvote 0
You are only looping through one cell/column, ALcol, eg if Lcol = 10, A10.

Try This.

For I = 1 To LCol

Columns(I).Columns(col.Column).TextToColumns _
Destination:=Cells(1, I), _
...
Next I
[/code]


Hi that does not work.
The column (i).columns part gives an error
 
Upvote 0
Oops, it should just be this.
Code:
Columns(I).TextToColumns.
 
Upvote 0
It doesn't really do anything in this case, unless you have "s in the data.
 
Upvote 0
Upvote 0
It doesn't really do anything in this case, unless you have "s in the data.

Hi,

What do they actually do (each one)
I have tried to see if there is a good explanation on that but cant find anything.

Any chance you can explain?

Ty
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
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