Text to Columns - Special delimiters

%3CLINGO-SUB%20id%3D%22lingo-sub-1281073%22%20slang%3D%22en-US%22%3EText%20to%20Columns%20-%20Special%20delimiters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281073%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20delimiter%20should%20I%20use%20to%20parse%20this%20bulk%20data%20download%20using%20the%20Text-to-Columns%20function%20in%20Excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20intended%20output%20is%26nbsp%3Bwhat%20is%20that%20which%20can%20be%20seen%20in%20cells%20H22-K22%2C%20which%20is%20data%20from%20G22.%20I%20did%20this%20using%20the%26nbsp%3BText-to-Columns%20function%20and%20entering%20Ctrl%20%2B%20J%20in%20the%20'Other'%20box%20as%20the%20delimiter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22orgnaise%20data.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182119iA08F1D978F819DBC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22orgnaise%20data.PNG%22%20alt%3D%22orgnaise%20data.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22pakistan.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182118iE8399D0A17CD628F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22pakistan.PNG%22%20alt%3D%22pakistan.PNG%22%20%2F%3E%3C%2FSPAN%3EHowever%2C%20to%20prepare%20the%20data%20in%20cell%20G22%20before%20using%20the%20Text-to-Column%20function%20I%20had%20to%20DOUBLE-CLICK%20the%20cell%20first.%20This%20caused%20linebreaks%20to%20appear%20between%20the%20country%20and%20numbers%20(the%20Ctrl%20%2B%20J%20delimiter%20delimits%20data%20by%20linebreaks).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%C3%9Asing%20Ctrl%20%2B%20J%20doesn't%20work%20on%20those%20others%20cells%20in%20column%20G.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20what%20might%20work%20for%20those%20other%20cells...%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20thinking%20is%20that%20although%20there%20are%20no%20visible%20spaces%2Fcharacters%2Flinebreaks%20between%20the%20countries%20and%20numbers%20in%20the%20rest%20of%20the%20cells%20in%20column%20G%2C%20there%20might%20be%20hidden%20ones%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20would%20explain%20why%20when%20I%20double-clicked%20G22%20linebreaks%20appeared%20in%20the%20correct%20positions%20between%20countries%20and%20numbers.%20Normally%20one%20would%20need%20to%20enter%20Ctrl%20%2B%20Enter%20to%20add%20line%20breaks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20maybe%20there's%20something%20besides%20Ctrl%20%2B%20J%20that%20would%20allow%20me%20to%20avoid%20the%20manual%20step%3F%20Because%20as%20this%20is%20bulk%20data%20-%20thousands%20of%20rows%20potentially%20-%20this%20would%20take%20me%20too%20long%20otherwise...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1281073%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-1292178%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20to%20Columns%20-%20Special%20delimiters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1292178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609006%22%20target%3D%22_blank%22%3E%40LouisHark%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20It's%20not%20necessary%20to%20double%20click%20every%20cell%2C%20you%20may%20apply%20Wrap%20Text%20to%20entire%20range%20to%20check%20which%20cells%20are%20with%20line%20breaks%3C%2FP%3E%0A%3CP%3E2)%20That%20could%20be%20tons%20of%20ideas%20what%20the%20delimiter%20is%2C%20better%20to%20check%20what%20it%20actually%20is%20with%20CODE()%20and%20MID()%20functions%20or%20some%20other%20way.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

Hi, 

What delimiter should I use to parse this bulk data download using the Text-to-Columns function in Excel?

 

My intended output is what is that which can be seen in cells H22-K22, which is data from G22. I did this using the Text-to-Columns function and entering Ctrl + J in the 'Other' box as the delimiter.

 

orgnaise data.PNGpakistan.PNGTo prepare the data in cell G22 before using the Text-to-Column function I DOUBLE-CLICKED the cell first. This caused linebreaks to appear between the country and numbers (the Ctrl + J delimiter delimits data by linebreaks), as you can see in the above example.

 

Because there are no line breaks in those other cells in column G, Ctrl + J doesn't work as a delimiter.

 

Any ideas what delimiter might work for those other cells...??

 

Although there are no visible spaces/characters/linebreaks between the countries and numbers in the rest of the cells in column G, there might be hidden ones?

 

That might explain why when I double-clicked G22 linebreaks appeared in the correct positions between countries and numbers? Normally one would need to enter Ctrl + Enter to add line breaks.

 

Because as this is bulk data - thousands of rows potentially - doubel-clcking all the cells in column G would take me too long unf. 

 

Many thanks!!

1 Reply
Highlighted

@LouisHark 

1) It's not necessary to double click every cell, you may apply Wrap Text to entire range to check which cells are with line breaks

2) That could be tons of ideas what the delimiter is, better to check what it actually is with CODE() and MID() functions or some other way.