Forum Discussion
Solved
need to separate words and numbers of one cell (with no character splitting) into several cells
Hello I have data’s that are in one cell combining letters and numbers. Now I like them separated in cells. The sample of content of cells is: WABERN3084WERKSTRASSE20 BIRRWIL5708SCHWADERHOF3 ...
Hi Richard,
If assume your first number always has 4 digits when it could be sequentially like
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1) =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),4) =MID(A1,LEN(B1)+LEN(C1)+1,LEN(A1)-LEN(B1)-LEN(C1)-LEN(E1)) =RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1))&"0123456789"))+1)Please see in attached file
Hi Richard,
If assume your first number always has 4 digits when it could be sequentially like
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),4)
=MID(A1,LEN(B1)+LEN(C1)+1,LEN(A1)-LEN(B1)-LEN(C1)-LEN(E1))
=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1))&"0123456789"))+1)
Please see in attached file
Hi Sergei
Perfect help and solution.
That was realy a great help to me.
Thanks a lot!!!!!!!!!!!!!!!!!!!
Hi Richard, glad to help