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.

 

[Jonmo1]

ok,

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

Change A1 to any cell that contains a string that contains line breaks.
Change 5 to whatever the position number in that string is a line break.

Here's a string:
Hello anxoperez

the letter o is in the 5th position of that string..
So =CODE(MID(A1,5,1)) would return 111 (the ASCII code for the letter o)

Now that formula will tell you the ASCII code for the character that looks like a line break.
Use that code when you go to find/replace
in the Find What box, Hold ALT and type that code.

 

[StuLux]

What jonmo1 is saying is to put this formula somewhere on your sheet but change the reference A1 to one of your cells that contains the line breaks and change the 5 to the number that represents the position that the 1st line break occurs e.g. if the cell containing the text with lne breaks is in cell AG2 and the 1st line break is at character position 12 the formula would be:

=CODE(MID(AG2,12,1)

This formula should then give you a number which is the number to then use in his previous reponse i.e. if the formula returns the value 7 then you would do a search and replace by holding down the ALT key and typing 007 (and replace with nothing) and therefore remove all line breaks from your sheet.

 

[Rick Rothstein]

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.

The ALT+010 is a PC "thing", so I doubt it will work on a Mac. I don't know the Mac's equivalent to the PC's CTRL key, but on a PC you can use CTRL+J in place of the ALT+010 to do the same thing... try your equivalent of that and see if it will work on the Mac.

 

[anxoperez]

Actually,

Perhaps Ricky is right, but I don't even have the privilege of making it that far.

I have understood both of the previous explanations and I have done what you guys have told me, but the formula returns an error.

I was out of time and so I had to do the substitutions manually.

Still, I have had this problem many times and I think it would be interesting to find out the answer.

If you guys are still willing to find solutions, I will gladly listen.

 

[frolicols]

It may be that on Mac it's a different code for line breaks? I know character 13 is also a line break so try this (where A1 is the cell containing your text):

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

 

[anxoperez]

Same error.

 

[anxoperez]

I asked this question a few weeks ago and I still have not gotten an answer. Now I need to do another substitution job and I really need to get an answer for my question.

If someone is courageous enough to look into this, I would appreciate it.

 

[frolicols]

I asked this question a few weeks ago and I still have not gotten an answer. Now I need to do another substitution job and I really need to get an answer for my question.

If someone is courageous enough to look into this, I would appreciate it.

Unless you can provide a sample file I don't think I will be able to help.

 

[CharlesChuckieCharles]

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?

Try This

Open module in VBA

paste

Public Sub CleanofReturns()
Dim c As Range
'ActiveSheet.UsedRange.Select
For Each c In Selection
    If (Not IsNumeric(c.Value)) And (Not c.HasFormula) Then
 
        c.Value = Replace((c.Value), Chr(10), "/")
    End If
 
Next c
End Sub

Compile

save

Highlight (select) on spreadsheet data requiring conversion

run CleanofReturns macro!!

IT is NOT a spreadsheet function
its a macro for converting the text contents of a cell

it ignores formula's

 

[anxoperez]

I think the problem is the Chr(10).

I have a MAC and I believe that does not work. This is what we were talking about in the previous threads.

Am I wrong?