VBA Code

[GN0001]

I am looking for VBA code that takes out the Carriage Return Character. I have a column that carriage return is unevenly coming in each row. How to take it out? VBA Code?

 

[Hui]

Guity


what about Replace


Variable = Replace(Variable, chr(10),"")

or

Variable = Replace(Variable, chr(13),"")

 

[asa]

Hi Guity,

You can also use an in-cell formula.

=SUBSTITUTE(A1,CHAR(13),"")


Will do the same for cell A1.  Excel appears to remove the line break if you use either [code]CHAR(13) (carriage return) or CHAR(10)

(line feed).


If you want a space instead of a line break, you can use:

=SUBSTITUTE(A1,CHAR(13)," &#34[/code]


Asa

 

[GN0001]

Asa and Hui,


Thank you for the response. I have 3 carriage returns in one row, can I use SUBSTITUTE(A1,CHAR(13)," ") to remove all three in a row, while I have 50000 Rows?


If I want to use it in VBA, what would be the whole code?


Thank you very much.

Guity

 

[asa]

Hi Guity, glad to help!


Don't be afraid to try it


Yes, substitute handles multiple instances.

If you want make the change permanent,

Copy the new calculated column, and paste it back with Paste Special/Values to remove the formulas.


It might be possible to use search and replace to remove line breaks as well (Ctrl-H)