0

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.

Here is the screenshot after trying Ctrl+J

enter image description here

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Avinash
  • 533
  • 5
  • 15

2 Answers2

2

use ctrl+H to replace "ctrl+enter" with blank or other symbol such as "/" or "|"

Anabas
  • 346
  • 1
  • 7
  • When i press "ctrl+enter", I'm getting error saying `we couldn't find what you are looking. – Avinash May 20 '20 at 13:39
  • actually, the right way is Alt+10( the number which is in key pad area), I am not sure your laptop still keep it. So I suggest you to use crtl+enter, it works in my laptop. Here suggest another way, in col E, use `=SUBSTITUTE(C1,CHAR(10),"")`, drag the formula. to get the clean words without "alt+enter". then copy and paste it into column c special by value. – Anabas May 20 '20 at 13:58
  • Thanks. For now, I have enclosed double quote for that column and it resolved the issue. And yeah `SUBSTITUTE` seems be a alternate option. Thanks for that as well. +1 – Avinash May 20 '20 at 15:15
1

I think they a wrapped text cells. If so,

Use find and replace dialog box.

In the find field enter Ctrl + J. Nothing will appear. Let it Be.

In the Replace field Enter Space

Then Click Replace all

All line breaks in cells will be replaced by Spaces. Hence, only recommend this before saving as text. Then you can undo or close excel without further saving.

EDIT Not sure which is the character we are looking to replace. We can use VBA to find it and replace it in VBA. But remember VBA procedure can not be undone. So, better make a copy of the workbook if required.

Copy the function in VBA Module.

Public Function asciien(s As String) As String
' Returns the string to its respective ascii numbers
   Dim i As Integer
   For i = 1 To Len(s)
      asciien = asciien & CStr(Asc(Mid(s, i, 1)))
   Next i
End Function

Use the following procedure to find asciien for that cell.

Sub test()
Debug.Print asciien(Range("C3"))
End Sub

Try to locate the asciien for line break. Use the following procedure to replace the asciien with "|" in entire sheet.

Sub Macro1()
    Cells.Replace What:=Chr(10), Replacement:="|", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=Chr(13), Replacement:="|", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Reference link1 and link2

Naresh
  • 2,984
  • 2
  • 9
  • 15