Jan 14 2019 11:39 PM
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
WILA8492SCHOCHENSTRASSE7
AEGERTEN2558RAINSTRASSE2
ONEX1213ROUTDUGRANDLANCY166
ENTLEBUCH6162BLUMATTSTRASSE7
I like them automatically sorted per cell as following (first cell):
Cell 1 Cell2 Cell3 Cell4
WABERN 3084 WERKSTRASSE 20
Can you help?
Thanks a lot!
Jan 15 2019 05:10 AM
SolutionHi 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
Jan 15 2019 11:35 PM
Hi Sergei
Perfect help and solution.
That was realy a great help to me.
Thanks a lot!!!!!!!!!!!!!!!!!!!
Jan 16 2019 01:41 AM
Hi Richard, glad to help
Jan 15 2019 05:10 AM
SolutionHi 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