CODEWITHSUNDEEP
vba
satincorvo
satincorvo

Reputation: 173

replacing carriage returns with a space (or any other chr) in vba?

I was trying to follow this post for removing carriage returns but ran in to two problems:

1) returned an overflow error
2) his program only removed carriage returns, did not replace them.

I need to be able to replace carriage returns because currently, I have cells like this:

Apples
Oranges
Grapes

note, there is no "space" after each string. I need a space there in order to use the split() function. In the rest of my data sheet, I have cells that contain "Apples ", "Oranges ", "Grapes ". note the space after the string. These cells work very well with my program, it's the carriage returns that are giving me problems.

Upvotes: 1

Views: 6402

Answers (2)

Kostas K.
Kostas K.

Reputation: 8508

Or something like this:

items = Split(Replace(item, vbLf & vbCr, " "), " ")

Upvotes: 2

A.S.H
A.S.H

Reputation: 29362

May be this simple statement?

myRange.Replace vbLf, " "

It works on a single cell as well as on a multiple range.

Upvotes: 1

Related Questions