Format a number to display as alternative text

[Terrafutan]

Hello All

I am trying to format a set of cells, A1:A4 that have the following values.
A1=1; A2=2; A3=3; A4=4
I however want the displayed information to show up as "Very Low", "Low",
"High" and "Very High"

If I just type the text in, I cannot find a way to sort them in the
numerical order I like because it sorts alphabetically, so I have to put them
in as numbers to get the sort working. Is there anyway I can have them
display as text or can I leave them as text and build a custom sort list so
that they will sort in the order as above.

Thanks

[Patrick Molloy]

you can't do it this way. What you could do is have the cell next to the
number show a text value.

With number formats yuo can only have 3 textual items, buts thats really
awful and Excel will translate if it can, so High will become Hours
You could use an IF or a UDF
eg in B1 to B4 =IF(A1=4,"Very High",IF(A1="3","High".....etc
you also us a UDF which would make the formula clearer

=UDF_Format(A1)

Function UDF_Format(target As Range) As String
Select Case target.Value
Case 4
UDF_Format = "Very High"
Case 3
UDF_Format = "High"
Case 2
UDF_Format = "Low"
Case 1
UDF_Format = "Very Low"
Case Else
UDF_Format = ""
End Select
End Function



Function MyFormat(target as range) as string

eg


"Terrafutan" wrote:

> Hello All
>
> I am trying to format a set of cells, A1:A4 that have the following values.
> A1=1; A2=2; A3=3; A4=4
> I however want the displayed information to show up as "Very Low", "Low",
> "High" and "Very High"
>
> If I just type the text in, I cannot find a way to sort them in the
> numerical order I like because it sorts alphabetically, so I have to put them
> in as numbers to get the sort working. Is there anyway I can have them
> display as text or can I leave them as text and build a custom sort list so
> that they will sort in the order as above.
>
> Thanks