Change row delimiter for a specific column in Excel

Question

I have a excel file which contains data as below.

+-------+----------+-------------------------+-----------+
| ID    | Material | Description             | Attribute |
+-------+----------+-------------------------+-----------+
| 10189 | Plate    | Dia.650mm 3-blade       | TK-BL     |
+-------+----------+-------------------------+-----------+
| 11189 | Nut      | MatType:A,C,M           | TK-OP     |
|       |          | Drive:HollowShaft       |           |
|       |          | Seal:PreparedForWelding |           |
+-------+----------+-------------------------+-----------+
| 12189 | Screw    | Wave1Batch1             | TK-MJ     |
+-------+----------+-------------------------+-----------+
| 13189 | Bolt     | 387L-2MM                | TK-MK     |
+-------+----------+-------------------------+-----------+

I have to convert this into Text file (TAB Delimited). So when I saved this as text file, it is saving as

ID                      Material    Description         Attribute
10189                   Plate       Dia.650mm 3-blade   TK-BL
11189                   Nut         MatType:A,C,M   
Drive:HollowShaft           
Seal:PreparedForWelding TK-OP       
12189                   Screw       Wave1Batch1         TK-MJ
13189                   Bolt        387L-2MM            TK-MK

Since there are new line in the description, it is considering that as next line while converting. How to avoid this? I have 1000 of values similar to this in my excel. I tried replacing \n with | for the description column, but it showed excel cannot find match. Please suggest some solution to avoid this situation.

score 0 · Accepted Answer · answered May 20 '20 at 13:59

You can use the same technique that Excel uses to make .CSV files. If Excel saves a cell that already contains commas or new lines (CHAR(10)) or other junk characters, Excel will enclose the cell's contents in double-quotes. That way, when Excel reads the file back in, it won't bother to parse the material inside the double-quotes.

You can code import/export macros to do the same thing.

Change row delimiter for a specific column in Excel

1 Answers1