Hi John,
If the column is wide enough you would probably
notice that the data is left justified, which is a pretty
good hint that it is text. That is just the default you
can justify anything however you want.

Format the column as General (not Text)
then use the TrimALL macro on the column, see
http://www.mvps.org/dmcritchie/excel/join.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Saxman" <john.h.williams wrote
Earlier today Max kindly worked out a function for me as follows:-

The following data is in cell A1.
0/12-F

The functions below placed in cells B1, C1, D1 give the values 1, 2, F.


=MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""))-(3-COLUMNS($
A:A)),1)

=MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""))-(3-COLUMNS($
A:B)),1)

=MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""))-(3-COLUMNS($
A:C)),1)

I now need to convert the extracted data, 1, 2, F to other values. I have a
function for this in cell E1, namely,
=IF(AND(B1=1,B1<=9),CHOOSE(B1,9,7,5,0,0,0,0,0,0,) ,0)

The above should give the value 9 with 1 in cell B1, but it doesn't. Maybe
it's because it is piggy-backed onto another function?

When I manually type 1 into cell B1, I get the correct output in cell E1,
i.e.,9.

I have tried formatting the input and output cells to text, number etc., but
it makes no difference.