identify cells containing numbers and text

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
939
hello,

is there a custom function to identify whether a cell contains only numbers, only text or a combination of numbers and text?

thanks
Andy
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
The type function will tell you if it's a number or text. But won't identify if it's a mixture of numbers and text...It's a little trickier to do that...

Try this UDF..

Code:
Public Function DataType(C As Range)
Dim x As Long
If C.HasFormula Then DataType = "Formula "
If C = "" Then
    DataType = DataType & "Blank"
    Exit Function
End If
If IsNumeric(C) Then
    DataType = DataType & "All Numbers"
    Exit Function
End If
For x = 0 To 9
    If InStr(1, C, x) > 0 Then
        DataType = DataType & "Mixture"
        Exit Function
    End If
Next x
DataType = DataType & "All Text"
End Function

Then you can use a formula
=DataType(A1)
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
939

ADVERTISEMENT

brilliant jonmo!!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Hey, no problem. If you like that, I went and made it a little more robust..
It will now identify
All text
All numbers
Mixture
Number Stored As Text
Blank
Logical(True or False)
Error
Formula (further broken down as above)

Code:
Public Function DataType(C As Range)
Dim x As Long
Dim y As Variant
If C.HasFormula Then DataType = "Formula "
If IsError(C) Then
    DataType = DataType & "Error"
    Exit Function
End If
If C = "" Then
    DataType = DataType & "Blank"
    Exit Function
End If
If Application.IsLogical(C) Then
    DataType = DataType & "Logical"
    Exit Function
End If
On Error Resume Next
y = C * 1
On Error GoTo 0
If Not IsEmpty(y) Then
    If WorksheetFunction.IsText(C) Then
        DataType = DataType & "Number Stored As Text"
        Exit Function
    Else
        DataType = DataType & "All Numbers"
        Exit Function
    End If
End If
For x = 0 To 9
    If InStr(1, C, x) > 0 Then
        DataType = DataType & "Mixture"
        Exit Function
    End If
Next x
DataType = DataType & "All Text"
End Function
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
939

ADVERTISEMENT

great! even better.. I will challenge you though since you made the effort.. can you break it down further to:

"Formula returning a value"
"Formula returning a logical statement"
"Formula returning text"

:)
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
So
Formula All Numbers
Formula All Text
Formula Logical

Wasn't good enough? :LOL:

try changing

If C.HasFormula Then DataType = "Formula "

to

If C.HasFormula Then DataType = "Formula Returning "

You should be able to change all the other text strings to whatever you want...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

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
Top