Dyn. Arr. fx needed to handle numbers as text

[paulma1960]

Hi all, in the attached workbook:
1. The table has a list of Class Numbers and the corresponding Market Share Codes. Each Class Number has one or more Market Share Codes, and some Market Share Codes comprise a comma-delimited list.
2. A list of Class Numbers in use.
3. Class Numbers in use, transposed.
4. The split list of Market Share Codes for each Class Number in use.

The 3 Class Numbers in use represent the scenarios of the data in use:
1. a numeric value starting with a zero, ie, number as text
2. a numeric value ending with "+", also number as text
3. a numeric value

My problem pertains to the formulas in cells I5:K5. The formulas in cells I5 & K5 return the expected values but the formula in cell J5 should return "LDST" but isn't. I'm looking for a correction (or alternative) to the formulas in I5:K5 so that all three formulas work as expected.

All suggestions are appreciated. TIA, Paul

[romperstomper]

It should be LEFT(J4, 1)="0" not LEFT(J4, 0)="0"

[AliGW]

Try this:

Please Login or Register  to view this content.

[AliGW]

Any good? Or have you just gone with post #2 and not told us?

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[paulma1960]

@romperstomper, big face-palm. Thanks for that.

@AliGW, I like your improvement, that works fine thanks.

[AliGW]

Glad to have helped.