Processing multiple lines of text in range of cells which contain CR and LF

Hi,

I have a worksheet which is populated with approximately 10 cols of data with 36 rows of data.

Each cell "can" have multiple lines of text. I need to process each cell on the sheet to do the following:
'1-Replace LineFeed and Carriage returns with spaces
'2-However, if a LineFeed and/or Carriage return appears on a "line" all by itself then i want to just delete LF and CR

Example:
Before processing: Cell A1 contains the following:
ABCDEFG HIJKLM NOP.(cr)
(cr)(lf)
QRST UVWXY XAAAB(cr)(lf)


i want the value in cell to be processed to look like this
ABCDEFG HIJKLM NOP. QRST UVWXY XAAAB


any idea how to do this.......i am enclosing a file showing an example of what i have and what i want to convert it to.......

not sure how to do this.

no that wont work........i tried the replace thing.

problem is if you look at this
ABCDEFG HIJKLM NOP.(cr1)
(cr2)(lf2)
QRST UVWXY XAAAB(cr3)(lf3)

(cr1) gets replaced with a space
(cr2)(lf2) is removed and not replaced with anything just removed
(cr3)(lf3) is replaced with two spaces and will be later removed with a trim.

its the (cr2)(lf2) that i dont know how to deal with...........does this make sense what i am asking about?

Maybe:

Please Login or Register  to view this content.

nope that does not work. it just does not handle the case i mentioned previously where (CR) and/or (LF) are on an empty line...in this case they should be removed and NOT replaced with spaces.

Your code produces "two" spaces between "stuffects. Risk" and it should produce only one space.

thanks for trying.

this is a tough one ......not sure how to do it.

I think the two spaces were a result of typing. Try this on your sample.

Please Login or Register  to view this content.

shg your right........let me try it on some other cells i have .......

shg, if i wanted to duplicate your formula in vba how would i do it?

shg,

here is my code but it does not seem to work

Please Login or Register  to view this content.

i still keep getting two spaces not the one

i even tried this and it did not work........

Please Login or Register  to view this content.

i even tried this

Please Login or Register  to view this content.

very strange.......because i think that should have worked.

when i do the formula in the excel sheet

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

it works......only a single space but for some reason not when i use the vba code.......any ideas?

i am doing something wrong..........i think all of your suggestions should work just fine.........i am just doing something wrong........not sure what yet

thanks for your help

I had no problems with this VBA version of shg's formula:

Please Login or Register  to view this content.

Alf

Post the workbook with the code showing an example that doesn't work as you expect.

..................+1

hi all,

thanks for helping. turns out all i needed to do was as someone posted.

remove chr(10)'s and replace with single-space
remove chr(13)'s and replace with single-space
AND then remove all double-spaces and replace with a single space (had to create a loop until all doubles were gone)

the above seem to fix my problem.......

so thanks to all who helped.