Returning Description Based on Field Value

[jaydee]

Aloha,

I need help returning 3 different text descriptions depending on the value in column A.

If the value in column A is:

1) all letters = Truss
2) all numbers = Treating
3) Has a combination of letters and numbers = Lumber

Is it possible to have one formula to return these 3 different descriptions in column B? Please see attached for details.

Thank you,

[Paul_Hossler]

1. Some column A cells were converted by Excel to scientific notation, e.g. 5.4E+252

2. It seemed easier to make a user defined function instead of a long worksheet formula.

3. This is a little more complicated than I started with, but seems to run pretty fast. It can always be improved




Option Explicit
Function LetttersAndNumbers(S As String) As String
Dim iDigits As Long, iChar As Long

For iChar = 1 To Len(S)
Select Case Mid(S, iChar, 1)
Case "0" To "9"
iDigits = iDigits + 1
End Select
Next iChar

If iDigits = Len(S) Then
LetttersAndNumbers = "Treating"
ElseIf iDigits = 0 Then
LetttersAndNumbers = "Truss"
Else
LetttersAndNumbers = "Lumber"
End If
End Function

[jaydee]

Hi Paul,

Thank you so much for your help. I tried it out and it works great. Runs fast, everything I could ask for.

I did notice the scientific notations. When I export my raw data, some have a " ' " sign which I convert to numbers, but that won't affect my data.

Have a wonderful evening!