SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-315028%22%20slang%3D%22en-US%22%3Eneed%20to%20separate%20words%20and%20numbers%20of%20one%20cell%20(with%20no%20character%20splitting)%20into%20several%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315028%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20have%20data%E2%80%99s%20that%20are%20in%20one%20cell%20combining%20letters%20and%20numbers.%20Now%20I%20like%20them%20separated%20in%20cells.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EThe%20sample%20of%20content%20of%20cells%20is%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWABERN3084WERKSTRASSE20%3C%2FP%3E%3CP%3EBIRRWIL5708SCHWADERHOF3%3C%2FP%3E%3CP%3EWILA8492SCHOCHENSTRASSE7%3C%2FP%3E%3CP%3EAEGERTEN2558RAINSTRASSE2%3C%2FP%3E%3CP%3EONEX1213ROUTDUGRANDLANCY166%3C%2FP%3E%3CP%3EENTLEBUCH6162BLUMATTSTRASSE7%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20like%20them%20automatically%20sorted%20per%20cell%20as%20following%20(first%20cell)%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECell%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cell2%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%20Cell3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cell4%3C%2FP%3E%3CP%3EWABERN%26nbsp%3B%26nbsp%3B%26nbsp%3B%203084%26nbsp%3B%20%26nbsp%3B%20WERKSTRASSE%26nbsp%3B%26nbsp%3B%26nbsp%3B%2020%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%3F%3C%2FP%3E%3CP%3EThanks%20a%20lot!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315028%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319281%22%20slang%3D%22en-US%22%3ERe%3A%20need%20to%20separate%20words%20and%20numbers%20of%20one%20cell%20(with%20no%20character%20splitting)%20into%20several%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319281%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Richard%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319239%22%20slang%3D%22en-US%22%3ERe%3A%20need%20to%20separate%20words%20and%20numbers%20of%20one%20cell%20(with%20no%20character%20splitting)%20into%20several%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319239%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3EPerfect%20help%20and%20solution.%3C%2FP%3E%3CP%3EThat%20was%20realy%20a%20great%20help%20to%20me.%3C%2FP%3E%3CP%3EThanks%20a%20lot!!!!!!!!!!!!!!!!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-315155%22%20slang%3D%22en-US%22%3ERe%3A%20need%20to%20separate%20words%20and%20numbers%20of%20one%20cell%20(with%20no%20character%20splitting)%20into%20several%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315155%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Richard%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20assume%20your%20first%20number%20always%20has%204%20digits%20when%20it%20could%20be%20sequentially%20like%3C%2FP%3E%0A%3CPRE%3E%3DLEFT(A1%2CMIN(FIND(%7B0%2C1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%7D%2CA1%26amp%3B%220123456789%22))-1)%0A%3DMID(A1%2CMIN(FIND(%7B0%2C1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%7D%2CA1%26amp%3B%220123456789%22))%2C4)%0A%3DMID(A1%2CLEN(B1)%2BLEN(C1)%2B1%2CLEN(A1)-LEN(B1)-LEN(C1)-LEN(E1))%0A%3DRIGHT(A1%2CLEN(A1)-LEN(B1)-LEN(C1)-MIN(FIND(%7B0%2C1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%7D%2CRIGHT(A1%2CLEN(A1)-LEN(B1)-LEN(C1))%26amp%3B%220123456789%22))%2B1)%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20in%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E
Richard028
Occasional Contributor

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!

3 Replies
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

Hi Sergei

Perfect help and solution.

That was realy a great help to me.

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

Highlighted
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies