Replace with Carriage Return Not Working

[redhots4]

I'm on a Mac with Microsoft 365 / Excel for Mac.

In the Replace dialogue, I'm using '$#@' as the Find Value and CTRL+OPT+RETURN as the Replace value. The replace runs but $#@ is replaced with a space, not a carriage return as expected. I've searched high and low for alternatives to a carriage return but none of them are working. Is there a way to put CHAR(13) into the Replace value field? If I enter CHAR(13) in that field, Excel literally replaces '$#@' with 'CHAR(13)'.

Any ideas?

I'm able to successfully do this replace on a Windows PC, but Mac is my primary machine so I want to figure this out. Thanks in advance!

 

[Fluff]

Not sure if this works on a Mac, but try using Ctrl J in the replace box

 

[redhots4]

Not sure if this works on a Mac, but try using Ctrl J in the replace box

Have tried that but the Windows shortcut doesn’t work on a Mac. I just get a ‘beep’.

 

[Fluff]

Ok how about using Alt 010 but you need to use the numeric keypad.

 

[Alex Blakenburg]

Are you sure you want Char(13) ? Not sure if you picked up on Fluff's last suggestion but Ctrl+J which is the same as windows Alt+Enter is actually Char(10) and is a Line Feed character.
I noticed that you have used VBA in the past so how about:

Sub ReplaceWithCarriageReturn()
    ActiveSheet.UsedRange.Replace What:="$#@", Replacement:=ChrW(10), LookAt:=xlPart
End Sub

You should be able to use vbLf instead of ChrW(10) if you want to.

 

[redhots4]

Are you sure you want Char(13) ? Not sure if you picked up on Fluff's last suggestion but Ctrl+J which is the same as windows Alt+Enter is actually Char(10) and is a Line Feed character.
I noticed that you have used VBA in the past so how about:

Sub ReplaceWithCarriageReturn()
    ActiveSheet.UsedRange.Replace What:="$#@", Replacement:=ChrW(10), LookAt:=xlPart
End Sub

You should be able to use vbLf instead of ChrW(10) if you want to.

Thanks for the suggestion, I will try this. Any ideas on how to do this within the Replace dialog itself?

 

[Alex Blakenburg]

Unfortunately unless the Alt 010 method that Fluff suggested in post #4 works on a Mac, I was unable to find any posts providing an alternative for a Mac. (I am on a windows laptop)