Hard Enter in one column values

  • Thread starter Thread starter Unnati
  • Start date Start date
U

Unnati

Hi,
I am trying to convert XLS to CSV and then load to Oracle. But in XLS i
have some field value with Carriage Return. So it is making new records. Is
there anyway i can do while converting? I have couple of them coming
frequently.
 
Select your data in Excel, then fire up the VBA Editor (Alt+F11).
Copy following line to the "Immediate" Window and push Enter:
Selection.Replace Chr(10), " "

This will replace all Carriage Returns in the selection with a Space
(substitute " " with "" if you just want to remove).

If you need to do this regularly, you can put it into a proper macro:

Sub RemoveReturns()
Selection.Replace Chr(10), " "
End Sub

Cheers,

Joerg Mochikun
 
You could also do the change via the user interface.

Select the range to fix
Edit|replace
what: ctrl-j
with: (space character)
replace all

========
I don't use oracle, but if it supports newlines in its fields, you may want to
change the alt-enter to a unique character (like a |). Import this data into
Oracle and then do something(?) in oracle to change that character back to a
linefeed.
 
Back
Top