Forum Discussion

Richard028's avatar
Richard028
Copper Contributor
Jan 15, 2019

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

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!

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 15, 2019

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jan 15, 2019

    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

    • Richard028's avatar
      Richard028
      Copper Contributor
      Jan 16, 2019

      Hi Sergei

      Perfect help and solution.

      That was realy a great help to me.

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

Resources