View Full Version : [SOLVED:] Returning Description Based on Field Value
jaydee
01-11-2017, 12:55 PM
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
01-11-2017, 02:07 PM
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
01-11-2017, 06:55 PM
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.