Delimit alpha and numercial data

[Manexcel]

I have one column (9000+ rows) thatcontains data prefixed; Ann or Annn or Annnn chars. e.g. Z12 and/or Z123 and/or Z1234 which I wish to delimit into separate columns

A data example of one cell is; Z12,Z123,Y1234,Y5678,Z1111 up to around 10 occurrences

I havebeen using a formula to delimit text which is working great.

The formula I have been using is: =TRIM(MID​

(SUBSTITUTE(SUBSTITUTE($A1,",","")," ",REPT(" ",100)),1+100*COLUMNS($A:A)-100,100))​

​

But I havetried to use the same formula to delimit the above challenge (Alpha and numeric chars). Itappears to work fine for the exception of data in my cells that is prefixedZ1000 or Z1024.

Can anyone assist?

Many thanks for your time and assistance.
​

 

[arthurbr]

Data - Text to columns - use the comma as delimiter?

 

[Rick Rothstein]

I would use Text To Columns like arthurbr suggested above; however, if you need a formula solution because the data in Column A will be changing often and you want the cells to update live, then give the following formula a try. With your data in Column A starting on Row 1, put this formula in cell B1 and copy it across for, say, 15 cells (that should cover your "around 10 occurrences" statement and then some), then copy all of those formulas down to the bottom of your data (or beyond if you want to cover additional rows of data being entered in the future)...

=TRIM(MID(SUBSTITUTE(","&$A1,",",REPT(" ",99)),COLUMNS($B:B)*99,99))

 

[Manexcel]

Thank you so much for the quick response. I have used the formula as I required 'live update' and it worked just great.

Thanks again.