need to separate words and numbers of one cell (with no character splitting) into several cells

[Richard028]

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!

[#2 · Accepted solution]

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

[#3]

Hi Sergei

Perfect help and solution.

That was realy a great help to me.

Thanks a lot!!!!!!!!!!!!!!!!!!!

[#4]

Hi Richard, glad to help