Splitting Cells With Data Via Formula

[James Russell]

Alright guys and girls,

I am attempting to divide a cell which value consists always of one letter and one number in the style A1 or H4 or M7 etc, etc. From this I wish to place the letter component in one worksheet column and the number in another. So far the best i have is this:

=ROUND((INT(A1)-37827000)/65536)

Where A1 is the database lookup which sources the record field containing the strange number/letter combo. At the moment this produces a series of numbers 0, 1, 2 for A, B, and C respectively. This method has several flaws, most commonly being that everytime a new value appears in the A1 column the =int() values shift ever so slightly. Also the fact that I have no way of converting the number which I am left with back to the character reference, ie. 0=A 1=B 2=C.

Can anyone improve this or find a far better and cleaner solution?

Thanks,

J