• 542K Members
• 4,237 Online
• 645K Conversations
SOLVED

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

Occasional Contributor

# 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

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!

3 Replies
Highlighted
Solution

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

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)```

Highlighted

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

Hi Sergei

Perfect help and solution.

That was realy a great help to me.

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

Highlighted

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

Related Conversations
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Adding Display Zero as Dashes to Custom Excel Number Format
emmagreen in Excel on
1 Replies
How do I group rows in an excel table?
Marisa Medrano in Excel on
1 Replies