CODEWITHSUNDEEP
vbacsv
j.due
j.due

Reputation: 47

How to remove each line break character within a field in vba

Tell me how to solve this problem? Sorry for my english. I am from Ukraine. There is a text file in CSV format with endings of strings in unix-format.

List of fields:

1) ID;
2) Surname;
3) First name;
4) Middle name;
5) Date of birth;
6) Type and number of the document proving the identity;
7) Address of residence;
8) Registration address.

There are no tabs inside the field, but there is an unshielded newline character `("\ n")`.

The first field (ID) has a numeric type and is guaranteed not contain a newline character. How to write the script on vba that will remove each line break character inside the fields?

So that you better understand my request to help,here the example (in xls) wrong format wrong and correct correct.

Upvotes: 1

Views: 1231

Answers (1)

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

try this macro;

Sub Broken_line_remover()
' Removing carriage returns from text strings

Dim ws As Worksheet
Set ws = ActiveSheet
ws.Cells.Replace what:=Chr(10), replacement:=" "
Set ws = Nothing
End Sub

Upvotes: 1

Related Questions