Find Replace to insert Carriage Returns

[Bobichael]

Someone please save me from losing my mind.

I'm working with a document that has several cells which require double carriage returns within cells. The location of each space is marked with an @ sign. A few weeks ago, I found an article online that explained how to do a find replace where the find value was '@' and the replace value was a series of numbers/characters that created a carriage return. When I doubled the carriage return code I got my two spaces. If I remember correctly, the string that I entered still appeared in the cell, so I needed to clean up afterwards by replacing that string with ' ', but it still left the line break.

I thought I bookmarked the page, but I can't find it again.

PLEASE HELP!!! What is the value that creates a carriage return?

Michael

 

[Peter_SSs]

What is the value that creates a carriage return?

Try Ctrl+j

 

[VoG]

Find and Replace. In the top box enter @. In the lower box hold down ALT, type 010 on the numeric keypad thyen click Replace All.

 

[Bobichael]

Find and Replace. In the top box enter @. In the lower box hold down ALT, type 010 on the numeric keypad thyen click Replace All.

Thanks, but I can't seem to get that to work. I am working on a Win7 VM running on a MAC, so I'm not sure if the alt key on my keyboard works differently.

I know there was definitely a combination other than 010 that gave me the desired effect. I think it was a special character and a number.

 

[Peter_SSs]

Thanks, but I can't seem to get that to work. I am working on a Win7 VM running on a MAC, so I'm not sure if the alt key on my keyboard works differently.

I know there was definitely a combination other than 010 that gave me the desired effect. I think it was a special character and a number.

Did you try the Ctrl+j?
I don't know if it works on a Mac but it does the same as Alt+(numberpad)010 on a pc.

The Ctrl+j can also be doubled-up in the "Replace with" box which you seemed to want. I don't think you can do that with Alt+010 anyway.

 

[Rick Rothstein]

Did you try the Ctrl+j?
I don't know if it works on a Mac but it does the same as Alt+(numberpad)010 on a pc.

The Ctrl+j can also be doubled-up in the "Replace with" box which you seemed to want. I don't think you can do that with Alt+010 anyway.

I would think Peter's suggested CTRL+J should work for you, but in case it does not, you should be able to use the following macro... simply select all the cells you want to process and then run this macro...

Sub ReplaceAtSignWithLineFeed()
  Dim Cell As Range
  For Each Cell In Selection
    Cell = Replace(Cell.Value, "@", Chr(10))
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ReplaceAtSignWithLineFeed) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

 

[Peter_SSs]

I would think Peter's suggested CTRL+J should work for you, but in case it does not, you should be able to use the following macro... simply select all the cells you want to process and then run this macro...

Sub ReplaceAtSignWithLineFeed()
  Dim Cell As Range
  For Each Cell In Selection
    Cell = Replace(Cell.Value, "@", Chr(10))
  Next
End Sub

Maybe?

Sub ReplaceAtSignWithLineFeed_v2()
  Selection.Replace What:="@", Replacement:=Chr(10), LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
End Sub

or, for the double-replace ..

Replacement:=Chr(10) & Chr(10)

 

[Rick Rothstein]

Maybe?

Sub ReplaceAtSignWithLineFeed_v2()
  Selection.Replace What:="@", Replacement:=Chr(10), LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
End Sub

No loop... good point. Another way (assuming the selection is contiguous)...

Sub ReplaceAtSignWithLineFeed_v3()
  Selection = Evaluate("IF(ROW(),SUBSTITUTE(" & Selection.Address & ",""@"",CHAR(10)))")
End Sub

 

[Bobichael]

Try Ctrl+j

Thank you so much. I was actually typing in ctrl+J, but I figured it out eventually.

Thanks again.

 

[Peter_SSs]

I was actually typing in ctrl+J,

Hehe, no wonder you thought it was no good to start with.

Glad you got there in the end.