Replacing line breaks within cells

[anxoperez]

I need to replace all the breaks within cells with the forward slash symbol

/

I have tried to do a find and replace by pasting the break symbol but that does not work (I remember doing that in Word a few years back and it used to work).

I need to do this substitution in Column AG and AH.

If someone can help.

 

[StuLux]

Do the cells contain more than one break, if so how many at most?

 

[StuLux]

This formula will remove a single line break in cell A1 by returning the characters either side of the line break. The line break is represented by CHAR(10).

=MID(A1,1,FIND(CHAR(10),A1,1)-1)&" "&MID(A1,FIND(CHAR(10),A1,1)+1,LEN(A1))

 

[frolicols]

If A1 has your text:

=SUBSTITUTE(A1,CHAR(10),"/")

 

[anxoperez]

I don't know the exact number of breaks, but it is under 5.

Can I still use your code for the columns where I need to conduct the substitution? If so, how?

 

[Jonmo1]

I need to replace all the breaks within cells with the forward slash symbol

/

I have tried to do a find and replace by pasting the break symbol but that does not work (I remember doing that in Word a few years back and it used to work).

I need to do this substitution in Column AG and AH.

If someone can help.

In Find/Replace..
Try holding down the ALT key and typing 010 in the find what box.

 

[StuLux]

Wow - so simple - never seen that one before and I was trying to come up with some VBA that would do this!

 

[anxoperez]

Unless I have done something wrong, it hasn't worked.

I suppose what you have suggested depends on what type of keyboard layout you're using, and I'm using several. Maybe you can paste what the actual symbols I should be typing are so I can try to find them.

I'm using a Mac. I don't know if that changes anything.

I am a bit desperate to get this done today. If you guys can help, that would be great.

 

[Jonmo1]

Try this

=CODE(MID(A1,5,1))

A1 is a cell with line breaks
change the 5 to a KNOWN position number of one of your line breaks.

Whatever that formula returns, is what you type when holding ALT in the find/replace window. In 3 digit format (leading 0 if needed).
So if that formula returns 10, type 010
If it returns 7, type 007
etc..

 

[anxoperez]

I am not sure exactly what I am supposed to do with that formula. I understand that is not VBA code, so I have just pasted it onto a random cell.

It returned an error and highlighted the A1 portion of the formula.

Maybe you can elaborate a bit more?