Trying to convert a text field to a specific number

[IronMike]

Is there a good formula out there? For example, our reporting outputs reports with the text Red, Yellow or Green. I can conditionally format those cells to turn those colors, but he powers to be would like icons only so it looks cleaner. I know, I know...What's the difference, but they would like it to show icons. Is there a formula out there to turn the text to a specific number? corresponding 1, 2 and 3 to Red, yellow and Green?

Thanks for any help. I got great assistance from this site with another question, so I am going back to the well!

IronMike

 

[Sam Mathai Chacko]

When you say 'turn', do you mean overwrite / replace? Or do you just want to use a formula to look-up the colors in one column, and in an other column, represent their corresponding number through a formula? If the latter, then you could use this

B1=MATCH(A1,{"Red","Yellow","Green"},0)

 

[Luke M]

You could certainly do that. I'd recommend creating a lookup table with a list of all the inputs/outputs you want, and then doing something like
=VLOOKUP(Lookup_value,Lookup_table,2,FALSE)

Where the lookup_table is the range of cells looking like

Red       1
Yellow   2
Green    3

 

[IronMike]

This is great info. I want to turn the actual text or color spelling into an actual number.

 

[Luke M]

I'm afraid formulas in XL don't allow you to overwrite the source data. If the user is inputting the words somewhere, all we can do is translate it into a number in a different cell.
That said, we could use some VB to change the cells after someone writes in them, but VB is not always desired.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
 
Dim x As Integer
'Turn this off so we don't trigger
'an endless seqeunce of events
Application.EnableEvents = False
 
With Target
    Select Case UCase(.Value)
        'What we want to change to
        Case "RED"
            .Value = 1
        Case "YELLOW"
            .Value = 2
        Case "GREEN"
            .Value = 3
        Case Else
            'do nothing, cell has something else
    End Select
End With
Application.EnableEvents = True
        
End Sub

 

[Sam Mathai Chacko]

You could manage using conditional format. Try this. You can figure out the logic by looking that the conditional format. If you aren't able to, but still want to use it, let me know.

 

[Luke M]

Nice Sam. To explain for others, the CF changes number format to:
;;;"l"
and the color of font as appropriate. The normal font of the cells has been changed to Wingdings.
Only downside I see is that if someone is trying to type into the cell the word "red", it will look funny (shows up fine in formula bar though).

 

[Sam Mathai Chacko]

Thanks for explaining the method Luke. Saved me time.

The Wingdings font can be changed back to the standard Arial (or any other regular font), and use a full-stop (period) in its stead. And increasing the font size a few notches will give the same feel as that of an icon.

 

[Sam Mathai Chacko]

Probably not the best substitute, but an asterisk might be better than a period. Well, I guess that also has it's limitations.

 

[IronMike]

You could manage using conditional format. Try this. You can figure out the logic by looking that the conditional format. If you aren't able to, but still want to use it, let me know.

This is perfect Sam. It worked like a charm! Thank you!