Yes, the data will always be AAANN. This code is ignoring less than 4
characters which is good, but it's only giving the number field. For some
reason, the text isn't showing up. It would actually be fine for the data to
appear in one cell. e.g. with ESV8, I should see V8. Any suggestions? And
thanks for the quick response.

"Bernard Liengme" wrote:

=IF(AND(ISTEXT(MID(A1,3,1)), ISNUMBER(--MID(A1,4,1))),--MID(A1,4,255),"")
Assuming always AAANN or AAA
Will not work with AAAAN
Please tell us your exact requirements
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"aileen" wrote in message
...
I have a column of data as such:
ESZ8
ESZ10
SPT
SPY
SPV
ESH9
SPC12

For the celss that have more than 3 characters I would like to separate
the
data into new columns as follows: ignore the first 2 characters then place
the 3rd character in a new column and any remaining characters which will
always be numbers in another column.

e.g
A B C
ESZ8 z 8
ESZ10 z 10
SPT
SPY
SPV
ESH9 h 9
SPC12 c 12

Is this at all possible? As usual, thank you for any help you can give.