Text to Columns - How to keep leading zeroes intact [SOLVED]

[polarisking]

Cell contents are Test|000123, Test1|067, and Test2|ABC.


I want to maintain 000123 and 067 as text values after splitting the cells using the "|" as a delimiter. I cannot figure out how to maintain the leading zeroes (it varies from cell to cell) using Text to Columns.


Any ideas?

 

[Colin Legg]

Hi,


Data > Text To Columns

Delimited > Next

Other Delimiter | > Next

In the data preview pane, left click on the second column and then at the top choose Text format for that column. > Finish

 

[imla]

here is picture:

http://s8.postimg.org/lo5kbcy85/Untitled.png

 

[polarisking]

Thanks to both of you. My dilemma is that rather than one delimiter (|), I have 100 and to step through 100 column text options is somewhat laborious - if that's the only solution, so be it. I'm hoping there's a general override.


Example


Test|000001|000002|0003|00004|000005|00006|000007|00008|00009|000010|000011|00012|00013|000014|00015|0016|00017|0018|0019|0020|00021|0022|0023|00024|00025|000026|00027|00028|0029|0030|0031|000032|00033|0034|0035|0036|00037|0038|000039|0040|0041|000042|0043|000044|000045|0046|0047|000048|00049|0050|000051|000052|0053|000054|0055|00056|0057|00058|0059|00060|0061|0062|0063|00064|0065|00066|0067|00068|0069|00070|000071|000072|0073|00074|00075|00076|0077|0078|0079|0080|00081|0082|00083|0084|0085|00086|000087|0088|00089|00090|00091|0092|00093|000094|00095|000096|0097|00098|000099|000100|00101

 

[Debraj]

Hi Polarisking..


Try this...

* Assuming you data is in TXT format..

* Copy all data and paste in WORD.

* Use Find & Replace..

Find : "|" (without Quote)

Replace with : "^t" (without Quote)


Now In excel

* Set all cells Property/Format to TEXT.

Now Copy all data from Word & paste as Text in Excel (use Alt E S T)..


Let us know the feedback..


Regards,

Deb

 

[polarisking]

Debraj, I'm very familiar with the technique, but hadn't thought of using it for this exercise. Once I take the string from Word, however, even though I've set the entire worksheet to TEXT, Excel puts on its "smarty pants" hat and converts the text numbers to numbers hence losing the leading zeroes.

 

[Colin Legg]

When you do the text to columns (following the steps I outlined), you can set all of the columns to text in one go. When you select the 2nd column in the preview pane, hold down the shift key and then click on the last column. This will select all of the columns apart from the 1st one. Then set the format to Text for them and click on finish.

 

[polarisking]

Colin, that works perfectly. Thank you.

 

[SirJB7]

Hi, polarisking!


Assuming your original string text is in cell A1, try this formula from B1:*1 as required:

=SI.ERROR(EXTRAE($A1&"|";HALLAR("";SUSTITUIR($A1&"|";"|";"";COLUMNA()-1))+1;HALLAR("";SUSTITUIR($A1&"|";"|";"";COLUMNA()))-HALLAR("";SUSTITUIR($A1&"|";"|";"";COLUMNA()-1))-1);"") -----> in english: =IFERROR(MID($A1&"|",SEARCH("",SUBSTITUTE($A1&"|","|","",COLUMN()-1))+1,SEARCH("",SUBSTITUTE($A1&"|","|","",COLUMN()))-SEARCH("",SUBSTITUTE($A1&"|","|","",COLUMN()-1))-1),"")


If character "" might exist in your string value replace it by another one that doesn't exist.


Just advise if any issue.


Regards!