identify cells containing numbers and text

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
946
Office Version
  1. 2019
Platform
  1. Windows
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

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
946
Office Version
  1. 2019
Platform
  1. Windows

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
946
Office Version
  1. 2019
Platform
  1. Windows

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

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
946
Office Version
  1. 2019
Platform
  1. Windows
i didn't notice you already done it! lol...great stuff
 
Master Excel Bundle

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

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