Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format of Text
I would like to format a cell based upon the length of the text information
in it. I am doing a lookup of information (numbers, but as text so leading zeros will show) of identifying codes. The code is either seven or eight digits long. The contents of the cell have the result from the LOOKUP function, so I can't see how to get anything from that to trigger the formatting. The cell needing the formatting gets its information from this formula: =LOOKUP($A5,Prolst!$C$6:$C$360,Prolst!$A$6:$A$360) &"-"&LOOKUP($A5,Prolst!$C$6:$C$360,Prolst!$B$6:$B$360 ) where $A5 is a three-digit product number. The result now is something like: 02054300-391526, or else 0054300-00101, and I would like to split it further as shown below. As far as I can tell, the Conditional format instruction only works on numeric data. Example: 0054300 would format as 00-54300 02054300 would format as 020-54300 the second half would show as 39152-6 (adding the dash) if it is six digits long, or does nothing if it is five digits long. -- Phil Grimes Plant Engineer Doumak, Inc. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format of Text
Consider using a Calculate Event macro to examine the contents of the cell
and apply the proper Custom format. -- Gary''s Student gsnu200703 "MM Phil" wrote: I would like to format a cell based upon the length of the text information in it. I am doing a lookup of information (numbers, but as text so leading zeros will show) of identifying codes. The code is either seven or eight digits long. The contents of the cell have the result from the LOOKUP function, so I can't see how to get anything from that to trigger the formatting. The cell needing the formatting gets its information from this formula: =LOOKUP($A5,Prolst!$C$6:$C$360,Prolst!$A$6:$A$360) &"-"&LOOKUP($A5,Prolst!$C$6:$C$360,Prolst!$B$6:$B$360 ) where $A5 is a three-digit product number. The result now is something like: 02054300-391526, or else 0054300-00101, and I would like to split it further as shown below. As far as I can tell, the Conditional format instruction only works on numeric data. Example: 0054300 would format as 00-54300 02054300 would format as 020-54300 the second half would show as 39152-6 (adding the dash) if it is six digits long, or does nothing if it is five digits long. -- Phil Grimes Plant Engineer Doumak, Inc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
Conditional format in a List | Excel Discussion (Misc queries) | |||
"Finalizing" format change to text | Excel Discussion (Misc queries) | |||
Copy & paste conditional format | New Users to Excel | |||
Formulas dealing with text data | Excel Worksheet Functions |