Split cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2421337%22%20slang%3D%22en-US%22%3ESplit%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2421337%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuppose%20this%20is%20a%20data.%20And%26nbsp%3BI%20want%20to%20split%20cell%20with%20the%20help%20of%20formula.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(3662).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286789iEC8FBE34938517C4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(3662).png%22%20alt%3D%22Screenshot%20(3662).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20should%20be%20split%20like%20this%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(3663).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286790iE5EF218D5435D6C1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(3663).png%22%20alt%3D%22Screenshot%20(3663).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2421337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2421376%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2421376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20two%20different%20suggested%20solutions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESteps%20are%20in%20German%2C%20but%20as%20a%20.gif%20file%20you%20can%20easily%20see%20the%20way.%20Fished%20from%20the%20German%20Internet%2C%20I%20didn't%20know%20exactly%20from%20where%2C%20probably%20from%20some%20public%20conversation.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bild_Komma_Splitting.gif%22%20style%3D%22width%3A%20912px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286792i79EACD536576B9CA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Bild_Komma_Splitting.gif%22%20alt%3D%22Bild_Komma_Splitting.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20other%20suggested%20solution%20is%20in%20the%20inserted%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2421388%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2421388%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20response%20sir.%3CBR%20%2F%3EBut%20I%20want%20according%20to%20screenshot%20-%3CBR%20%2F%3EKant%20Str.%20(should%20be%20in%20Name%20column)%3CBR%20%2F%3E46%20(should%20be%20in%20Age%20column)%3CBR%20%2F%3EBerlin%20(should%20be%20in%20Place%20column%20)%3CBR%20%2F%3EGermany(should%20be%20in%20Country%20column)%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20help...%3F%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2421436%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2421436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20english%3C%2FP%3E%3CH2%20id%3D%22toc-hId--441195433%22%20id%3D%22toc-hId--441195433%22%20id%3D%22toc-hId--441195433%22%20id%3D%22toc-hId--441195433%22%20id%3D%22toc-hId--441195433%22%20id%3D%22toc-hId--441195433%22%20id%3D%22toc-hId--441195434%22%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F1786-excel-split-text-by-space.html%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20To%20Split%20Text%20By%20Space%2FComma%2FDelimiter%20In%20Excel%3F%3C%2FA%3E%3C%2FH2%3E%3CP%3E%3CSPAN%3EIf%20you%20have%20a%20column%20list%20of%20data%20and%20you%20want%20to%20split%20them%20into%20several%20columns%20by%20a%20specific%20delimiter%20just%20like%20the%20below%20screenshots%20shown%2C%20how%20can%20you%20split%20them%20in%20Excel%3F%20Maybe%20some%20of%20users%20think%20of%20the%20Text%20to%20Column%20function%20only%2C%20but%20now%20I%20will%20introduce%20not%20only%20Text%20to%20Columns%20function%2C%20but%20also%20a%20VBA%20code%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20for%20self-help.%3C%2FP%3E%3CP%3EI%20am%20a%20simple%20user%2C%20who%20simply%20offered%20his%20knowledge%20and%20time%20voluntarily%2C%20with%20no%20guarantee.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2421478%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2421478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3BYou%20start%20by%20saying%20%22Suppose%20this%20is%20the%20data%22.%20That%20could%20mean%20that%20your%20real%20data%20isn't%20like%20in%20your%20screenshots%20at%20all.%20But%20let's%20suppose%20it%20is%2C%20then%20you%20could%20go%20through%20a%20number%20of%20Text-to-column%20steps%2C%20manually%2C%20or%20you%20use%20Power%20Query%20(PQ).%20The%20attached%20file%20contains%20a%20PQ%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20split%20by%20the%20comma.%20This%20will%20create%20three%20columns%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-07%20at%2012.24.57.png%22%20style%3D%22width%3A%20528px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286819iF2A4326D99541439%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-07%20at%2012.24.57.png%22%20alt%3D%22Screenshot%202021-06-07%20at%2012.24.57.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20split%20the%20first%20column%20at%20the%20point%20where%20a%20character%20changes%20to%20a%20number%20(digit)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-07%20at%2012.28.53.png%22%20style%3D%22width%3A%20688px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286817i4491753882320EF3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-07%20at%2012.28.53.png%22%20alt%3D%22Screenshot%202021-06-07%20at%2012.28.53.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20add%20a%20custom%20column%20that%20excludes%20any%20number%20from%20the%20field%20that%20contains%20the%20city%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-07%20at%2012.29.05.png%22%20style%3D%22width%3A%20845px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286814i0EBC5B101EC9B8FD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-07%20at%2012.29.05.png%22%20alt%3D%22Screenshot%202021-06-07%20at%2012.29.05.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrim%20the%20custom%20column%2C%20delete%20the%20original%20column%20with%20the%20city%20information%20and%20reorder%20the%20remaining%20columns%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-07%20at%2012.36.25.png%22%20style%3D%22width%3A%20682px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286820iBE8E60B2B80EC6E6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-07%20at%2012.36.25.png%22%20alt%3D%22Screenshot%202021-06-07%20at%2012.36.25.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELoad%20the%20end%20result%20back%20to%20Excel%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-07%20at%2012.30.28.png%22%20style%3D%22width%3A%20582px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286813i082D2B7429E703DB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-07%20at%2012.30.28.png%22%20alt%3D%22Screenshot%202021-06-07%20at%2012.30.28.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2421494%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2421494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20449px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286824i0A0335F8ECE5AB83%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTRANSPOSE(FILTERXML(%22%3CR%3E%3CN%3E%22%20%26amp%3B%20SUBSTITUTE(REPLACE(A2%2CSEARCH(%22%20%22%2C%20A2%2C%20SEARCH(%22%20%22%2CA2)%2B1)%2C1%2C%22%2C%22)%2C%22%2C%22%2C%22%3C%2FN%3E%3CN%3E%22)%20%26amp%3B%20%22%3C%2FN%3E%3C%2FR%3E%22%2C%20%20%22%2F%2Fn%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20it%20very%20depends%20on%20source%20text%20logic.%3C%2FP%3E%0A%3CP%3EAnd%20that's%20an%20array%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2421520%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2421520%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20confirm%20have%20you%20done%20with%20the%20help%20of%20Transpose%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESir%20can%20you%20split%20cell%20same%20thing%20with%20the%20help%20of%20SUBSTITUTE%20function%20with%20TRIM%20function%3F%3F%3F%3C%2FP%3E%3CP%3EBut%20it%20should%20be%20dynamic..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20attached%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hello Everyone,

 

Suppose this is a data. And I want to split cell with the help of formula.

Screenshot (3662).png

 

It should be split like this - 

Screenshot (3663).png

 

Please help.??

 

Here is a attached file..

13 Replies

@Zan_Hanifee 

Here are two different suggested solutions.

 

Steps are in German, but as a .gif file you can easily see the way. Fished from the German Internet, I didn't know exactly from where, probably from some public conversation.

Bild_Komma_Splitting.gif

 

The other suggested solution is in the inserted file.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Thank you for the response sir.
But I want according to screenshot -
Kant Str. (should be in Name column)
46 (should be in Age column)
Berlin (should be in Place column )
Germany(should be in Country column)

Please help...??

@Zan_Hanifee 

 

In english

How To Split Text By Space/Comma/Delimiter In Excel?

If you have a column list of data and you want to split them into several columns by a specific delimiter just like the below screenshots shown, how can you split them in Excel? Maybe some of users think of the Text to Column function only, but now I will introduce not only Text to Columns function, but also a VBA code

 

Help for self-help.

I am a simple user, who simply offered his knowledge and time voluntarily, with no guarantee.

 

Thank you for your understanding and patience

 

 

Nikolino

I know I don't know anything (Socrates)

 

@Zan_Hanifee You start by saying "Suppose this is the data". That could mean that your real data isn't like in your screenshots at all. But let's suppose it is, then you could go through a number of Text-to-column steps, manually, or you use Power Query (PQ). The attached file contains a PQ solution.

 

First split by the comma. This will create three columns:

Screenshot 2021-06-07 at 12.24.57.png

 

Then split the first column at the point where a character changes to a number (digit):

Screenshot 2021-06-07 at 12.28.53.png

 

Then add a custom column that excludes any number from the field that contains the city:

Screenshot 2021-06-07 at 12.29.05.png

 

Trim the custom column, delete the original column with the city information and reorder the remaining columns:

Screenshot 2021-06-07 at 12.36.25.png

 

Load the end result back to Excel:

Screenshot 2021-06-07 at 12.30.28.png

 

@Zan_Hanifee 

As variant that could be

image.png

with

=TRANSPOSE(FILTERXML("<r><n>" & SUBSTITUTE(REPLACE(A2,SEARCH(" ", A2, SEARCH(" ",A2)+1),1,","),",","</n><n>") & "</n></r>",  "//n"))

but it very depends on source text logic.

And that's an array formula.

@Sergei Baklan 

Please let me confirm have you done with the help of Transpose formula?

 

 

 

Sir can you split cell same thing with the help of SUBSTITUTE function with TRIM function???

But it should be dynamic..

 

Please help..???

 

 

Here is a attached file

 

@Zan_Hanifee 

First we replace every second space with comma. After that FILTERXML() splits text prepared by SUBSTITUTE() using commas as separators. TRANSPOSE() only shows result in the row, not in column.

 

If you are on Excel which support dynamic arrays (365) it works as it is. Otherwise array formula with Ctrl+Shift+Enter shall be used.

@Sergei Baklan 

Yes sir, it support dynamic arrays.

 

So, what formula should i write sir?

 

@Zan_Hanifee 

When it works as it is. Just open the file attached to my post to check.

Thank you so much sir

@Riny_van_Eekelen 

Sir can we do with the help of formula?

 

If yes,

please help..??

 

Here is a attached file

@Zan_Hanifee Not sure what you are after. The file you attached is the same as earlier. I provided a Power Query solution that does exactly what you asked for, and @Sergei Baklan provided a formula solution. The only difference is that his formula does not exclude the numbers for entries like "10786 Berlin". Perhaps Sergei can fix that, as I'm not all that familiar with FILTERXML.

Ok, Thank you so much sir