Hello All, Just switched to mac and I am stuck in office Mac Excel with a find replace. Here is what I got:

* This product is greatZZZ* Save moneyZZZ* Buy it now

All viewable in one cell. I want to find ZZZ and replace with a carriage return to look like:

* This Product is great

* Save Money

* Buy it nowAny ideas for a new mac user?? thanks!!

I'm assuming you mean you want the string: * This product is greatZZZ* Save moneyZZZ* Buy it now

Broken into three separate parts and placed in different cells.

If your string is in Cell A1 then place the following in:

Cell A2 =LEFT(A1,FIND("Z",A1,1)-1)

Cell A3 =LEFT(RIGHT(A1,LEN(A1)-FIND("Z",A1,1)-2),FIND("ZZZ",RIGHT(A1,LEN(A1)-FIND("ZZZ",A1,1)-3),1))

Cell A4 =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"Z","#",LEN(A1)-LEN(SUBSTITUTE(A1,"Z","")))))

MIKE

all items are in the same cell and must remain that way.

You said you just switched to a Mac, implying you used to do this on a PC. If that's the case, how did you do it on a PC?

on pc it was so easy.... Find: "ZZZ"

Replace: alt+0010

ASCII code 10 is a Line Feed character,

Try 13 which is a Carriage Return char.MIKE

Thanks, but how is that typed properly in the replace field in excel?

Do other Alt Codes work as expected? Have you tried 010 instead of 0010?

Make sure you have Word Wrap set, else all you'll see is a little box. Both ASCII 10 and ASCII 13 work for me using Excel 2000.

MIKE

If you take the three formulas I posted in Response 1 and combine them with CHAR(10) you'll also get what your looking for.

Here is the whole formula, but I think the forum software might not wrap correctly:=LEFT(A1,FIND("Z",A1,1)-1)&char(10)&LEFT(RIGHT(A1,LEN(A1)-FIND("Z",A1,1)-2),FIND("ZZZ",RIGHT(A1,LEN(A1)-FIND("ZZZ",A1,1)-3),1))&char(10)&RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"Z","#",LEN(A1)-LEN(SUBSTITUTE(A1,"Z","")))))

MIKE

there is no "alt" key on a mac... that is how I got here. I hold down option and press 0010 and does not work.

Tried Option and Shift? This site seems to indicate that some ASCII codes use Option - Shift:

http://www.menekali.com/mac-alt-cod...

Also see the 1st response to the question asked here - the response explaining how to enable Unicode-Hex-Input.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History