• 517K Members
• 6,872 Online
• 613K 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
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!!!!!!!!!!!!!!!!!!!

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

Related Conversations
not able to type number and text in same cell?
Deleted in Excel on
2 Replies
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
Arabic numbers in OWA 15 with SharePoint 16
Deleted in SharePoint on
6 Replies