Text to columns

%3CLINGO-SUB%20id%3D%22lingo-sub-3520216%22%20slang%3D%22en-US%22%3EText%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3520216%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20facing%20a%20problem%20with%20separating%20the%20cell's%20contents%20to%20columns%2C%20as%20some%20of%20the%20values%20are%20sometimes%20preceded%20by%20a%20new%20line%20(Enter%20%22%2Fn%22)%20which%20makes%20it%20unrecognizable%20for%20the%20text%26gt%3Bcolumn%20function.%20can%20anybody%20help%20with%20this%3F%20How%20can%20I%20get%20rid%20of%20the%20%22Enter%22%20entries%20or%20maybe%20replace%20them%20with%20comas%3F!%20because%20the%20data%20size%20is%20huge%20and%20this%20same%20report%20is%20to%20be%20applied%20to%20a%20query%20so%20I%20need%20a%20systematic%20method.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3520216%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3520320%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3520320%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1427569%22%20target%3D%22_blank%22%3E%40Smabualhaija%3C%2FA%3E%26nbsp%3BWhen%20you%20say%20the%20data%20size%20is%20huge%2C%20you%20should%20consider%20Power%20Query.%20Are%20you%20familiar%20with%20that%3F%20If%20not%2C%20this%20is%20a%20good%20point%20to%20start%20learning.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3520325%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3520325%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BI%20am%20building%20a%20powerQuery%20model%2C%20but%20my%20problem%20is%20that%20I'm%20not%20able%20to%20separate%20the%20values%20into%20seperate%20columns%20because%20from%20the%20source%20they%20are%20not%20only%20separated%20by%20comas%2C%20but%20also%20by%20%22Enter%22%20or%20new%20line%20in%20some%20cases%2C%20so%20that's%20why%20I%20was%20asking%20for%20a%20reliable%20solution.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Smabualhaija_0-1655468816637.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Smabualhaija_0-1655468816637.png%22%20style%3D%22width%3A%2089px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Smabualhaija_0-1655468816637.png%22%20style%3D%22width%3A%2089px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F381274iA7DA73A4A9870E55%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Smabualhaija_0-1655468816637.png%22%20alt%3D%22Smabualhaija_0-1655468816637.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3520330%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3520330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1427569%22%20target%3D%22_blank%22%3E%40Smabualhaija%3C%2FA%3E%26nbsp%3BCan't%20tell%20how%20to%20do%20that%20without%20a%20test%20file.%20Can%20you%20share%20one%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm facing a problem with separating the cell's contents to columns, as some of the values are sometimes preceded by a new line (Enter "/n") which makes it unrecognizable for the text>column function. can anybody help with this? How can I get rid of the "Enter" entries or maybe replace them with comas?! because the data size is huge and this same report is to be applied to a query so I need a systematic method. 

 

thanks 

3 Replies

@Smabualhaija When you say the data size is huge, you should consider Power Query. Are you familiar with that? If not, this is a good point to start learning.

https://exceloffthegrid.com/power-query-introduction/ 

@Riny_van_Eekelen I am building a powerQuery model, but my problem is that I'm not able to separate the values into seperate columns because from the source they are not only separated by comas, but also by "Enter" or new line in some cases, so that's why I was asking for a reliable solution.

Smabualhaija_0-1655468816637.png

 

@Smabualhaija Can't tell how to do that without a test file. Can you share one?