how to convert

February 26, 2011 at 19:04:41
Specs: Windows Vista
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"?

See More: how to convert

Report •

February 26, 2011 at 21:00:53
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 be left aligned,
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.


Report •

February 27, 2011 at 18:10:28
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.

Report •

Related Solutions

Ask Question