Carriage Return characters removing - Interesting case

[rohit1978]

Hello,
We have an excel spreadsheet which users fill out and send it to us. We convert that in a CSV file and then send it to Oracle for uploading.

In excel file , we observed that there were some carriage return characters which would create issues while loading the data in oracle tables. so, we wrote following macro

Sub replacer()
Dim MyChar
MyChar = Chr(10)
Worksheets("Sheet1").Columns("A:A").Replace _
What:=MyChar, Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True

End sub

which replaces the carriage return from the cells. However, this macro didn't work for the cells which have their text copied from a webpage text area. Users had used enter key in the text area and while they copied the data from text area to excel, it looked like a carriage return key but since it was not removed by the macro, I am not sure which character that is.

Anyone with any possible directions to go about removing those apparent carriage return characters which may not be carriage returns in reality?

Please help,
Thanks so much,
Rohit

 

[Jonmo1]

You'll need to find out what character it is and adjust your code...

You can use the CODE function to find out the Character #

=CODE(LEFT(A1,1))

where the 1st character of A1 is the character you want to remove..

 

[VoG]

Also...

Chr(10) is actually a Line Feed. Carriage Return is Chr(13). you should also check for VbCrLf.

 

[Legacy 68668]

Try
Replace Chr(10) with vbNewLine

 

[rohit1978]

Hello,
Thanks for your replies. I checked the code of the character and it came out to be chr(10). My question is why it's not getting replaced with the code I have written?

Here is the sample text from my excel file - There are chr(10)s after every asterik but it won't get replaced by nothing by following code

Sub replacer()
Dim MyChar
MyChar = Chr(10)
Worksheets("Sheet1").Columns("A:A").Replace _
What:=MyChar, Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True


=====================================================
Sample Text
=====================================================
Sample Text - There are chr(10)s after asterik but they are not getting replaced :*
-- A;*
-- B;*
-- C;*
-- D)
=====================================================

 

[VoG]

This works for me

Sub replacer()
Dim MyChar
MyChar = Chr(10)
Worksheets("Sheet1").Columns("A:A").Replace _
What:=MyChar, Replacement:="", _
SearchOrder:=xlByRows, MatchCase:=True
End Sub

 

[rohit1978]

By rows didn't work for me. It took out the spaces in the entire text and all words were concatenated one after the other

 

[Norie]

Rohit

How are you converting to a CSV file?

Why not use a formula to get rid of the unwanted character(s) rather than find/replace?

Also are you sure there are no other unwanted characters?

PS Why do you have MatchCase:=True?