Formula ISNUMBER(MID(A1,2,1) returns "false" even tho the character is a number, and (MID(A1,2,1) RETURNS A number. I understand ISNUMBER returns a text "number" instead of a number. How can I get the first formula to return "true"?

The =MID() function is a TEXT operator,

when you use it on a number, it converts the number to text.If you put the number 123456 into cell A1

and then the formula =MID(A1,2,3) in cell B1

it will return 234, but not as a number but as text,

you can tell because the 123 will beleftaligned,

meaning it is text, as numbers are always right aligned.If you now copy the contents of cell B1

and do a paste special, value into another cell,

it will ask you if you want to convert the TEXT 123 into a number.MIKE

Thanks for reply. After posting question, I hit uponj an answer. I have roster of members of an amateur radio club listing some 100 members by their call signs

in alpha order. Call signs can start with any one of 4 or 5 letters and consist of one numeral from 0 to 9 in any one of the first 3 positions, followed by 1 to 3 letters. I want to sort them alpha by the letters following the numeral. Thus the need to separate these letters. After many trials, the following does it:

=(IF(ISNUMBER(MID(A1,1,1)),MID(A1,2,4),IF(ISNUMBER(VALUE(MID(A1,2,1) ) ),MID(A1,3,4),IF(ISNUMBER(VALUE(MID(A1,3,1) ) ),MID(A1,4,4)," "))))

Thanks again for the help.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History