Find Replace to insert Carriage Returns

Bobichael

New Member
Joined
Dec 11, 2012
Messages
7
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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

New Member
Joined
Dec 11, 2012
Messages
7
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,430
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
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...

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,430
Office Version
365
Platform
Windows
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...

Code:
Sub ReplaceAtSignWithLineFeed()
  Dim Cell As Range
  For Each Cell In Selection
    Cell = Replace(Cell.Value, "@", Chr(10))
  Next
End Sub
Maybe?
Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
Maybe?
Rich (BB code):
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)...
Rich (BB code):
Sub ReplaceAtSignWithLineFeed_v3()
  Selection = Evaluate("IF(ROW(),SUBSTITUTE(" & Selection.Address & ",""@"",CHAR(10)))")
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,747
Messages
5,446,255
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top