Excel experts help! I need to split this (10E78AW129) up into 5 cells?

Question

I have a cell with 10E78AW129 in it. I need a cell to be 10 then one to be E then one to be 7 etc. How the heck can I do this?

Answer 1

Assuming you want that separated into numbers and letters like so:
10
E
78
AW
129
then if 10E78AW129 is in A1, use these formulas in B1, C1, D1, E1, and F1:
=VALUE(LEFT(A1,2))
=MID(A1,3,1)
=VALUE(MID($A1,4,2))
=MID(A1,6,2)
=VALUE(RIGHT(A1,3))

Answer 2

If you are just working with the one sample, then it's easier to just do it manually. However, if you have many sample then it would make sense to use functions to split the samples up into the appropriate columns.

If you click on the function symbol in an empty cell and then click on all, then scroll down to text you will see a variety of different functions to chose from which can be used to extract the information you wish.

Depending on your needs and what your data looks like you can combine multiple functions, which will be nested in order to get the results you desire. The real power comes when you start incorporating logic functions into your statements.

I know, I haven't given you the answer as you had hoped somebody would, but I don't believe in doing someone's work for them. Instead, I point them in the direction and let them learn to do it on their own.

I hope this helps. Good luck.

Answer 3

click on the cell, select data/text to columns/fixed width/click next
click on the space in between where you want the letters to split

Answer 4

right click on cell , ater click there will be available a window of function , you will click on (format cell) function



after open the function of this window click on special after click zip after ok