• 466K Members
• 8,635 Online
• 563K 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)

# 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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies