# 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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### 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 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,517
Messages
5,837,802
Members
430,516
Latest member
thaling ### 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.

### Which adblocker are you using?    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