identify cells containing numbers and text

neodjandre

Well-known Member
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.

arthurbr

Well-known Member
Hi,
does the TYPE function help?

yes.. thanks!

Jonmo1

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

brilliant jonmo!!

Jonmo1

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

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
So
Formula All Numbers
Formula All Text
Formula Logical

Wasn't good enough?

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

neodjandre

Well-known Member
i didn't notice you already done it! lol...great stuff

Replies
1
Views
121
Replies
3
Views
42
Replies
11
Views
314
Replies
12
Views
198
Replies
7
Views
194

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.

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

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