ALT+0010 for Mac Excel / find replace

April 30, 2009 at 10:47:23
Specs: Macintosh
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 now

Any ideas for a new mac user?? thanks!!


See More: ALT+0010 for Mac Excel / find replace

Report •


#1
April 30, 2009 at 11:43:19
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

http://www.skeptic.com/


Report •

#2
April 30, 2009 at 11:49:39
all items are in the same cell and must remain that way.

Report •

#3
April 30, 2009 at 12:04:23
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?


Report •

Related Solutions

#4
April 30, 2009 at 12:10:19
on pc it was so easy....

Find: "ZZZ"

Replace: alt+0010


Report •

#5
April 30, 2009 at 12:35:26
ASCII code 10 is a Line Feed character,
Try 13 which is a Carriage Return char.

MIKE

http://www.skeptic.com/


Report •

#6
April 30, 2009 at 12:48:26
Thanks, but how is that typed properly in the replace field in excel?

Report •

#7
April 30, 2009 at 12:48:31
Do other Alt Codes work as expected?

Have you tried 010 instead of 0010?



Report •

#8
April 30, 2009 at 13:04:59
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

http://www.skeptic.com/


Report •

#9
April 30, 2009 at 13:17:50
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

http://www.skeptic.com/


Report •

#10
April 30, 2009 at 13:20:38
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.

Report •

#11
April 30, 2009 at 13:39:55
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.

http://forums.macnn.com/90/mac-os-x...


Report •


Ask Question