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

 

[Siyanna]

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

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?

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

 

[Norie]

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]

 

[Siyanna]

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

 

[Norie]

Oops, it should just be this.

Columns(I).TextToColumns.

 

[Siyanna]

Oops, it should just be this.

Columns(I).TextToColumns.

Ty

Can you please explain what text qualifier does "" ' {none}
I have no idea what each one does

Ty

 

[Norie]

It doesn't really do anything in this case, unless you have "s in the data.

 

[Rick Rothstein]

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?

Does the UDF code I posted here help you any?

http://www.excelfox.com/forum/f2/count-no-cells-containing-date-379/#post1377

If you pass in a single cell reference, then 1 means it is a real Excel date and 0 means it is not.

 

[Siyanna]

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

 

[Siyanna]

Does the UDF code I posted here help you any?

http://www.excelfox.com/forum/f2/count-no-cells-containing-date-379/#post1377

If you pass in a single cell reference, then 1 means it is a real Excel date and 0 means it is not.

Ty

Il give that a go and will let you know.
What dies the .text part do?

 

[Rick Rothstein]

Il give that a go and will let you know.
What dies the .text part do?

It returns the text that is displayed in the cell itself, not the value that is behind it (which can be different).