Solved Replace with line break (Excel for Mac)

April 10, 2011 at 13:26:47
Specs: Macintosh
Hi all,
I have a document with over 1,000 subtitles for a film (I have it in both Word and Excel versions).
Most of the subtitles have 2 lines, which are separated with this character: |
In Word (Windows or Mac), I can easily replace all the | with a line break (^p).
How can I do it in Excel for Mac?
Is there a way to represent the line break within the Excel "Replace" tool, just like ^p in the Word "Replace" tool?
Or is there a way to paste a Word table (which has line breaks within the table cells) into Excel, keeping the line breaks?
Thanks!
Enrique.

edited by moderator: email address removed


See More: Replace with line break (Excel for Mac)

Report •


✔ Best Answer
April 10, 2011 at 20:55:40
FOUND IT!
Indeed, I had to put the Spanish expression:

=SUSTITUIR(C185;"|";CARACTER(13))

I hope this helps future visitors.

Gracias amigos!! I didn't even know the "SUBSTITUTE/SUSTITUIR" function.



#1
April 10, 2011 at 15:02:29
Please remove your email address from your post, unless you really like getting spamed.
There are little bots that troll the web looking in open forums for emails address's
Use the edit button in the upper right corner.


Sorry, don't know that much about Mac's

In Windows the key sequence is:
ALT + ENTER
and I believe that the command for Mac's is:
CONTROL+OPTION+RETURN

Do not know if the Mac's search & replace will accept the key sequence.

The =CHAR() function could also be used.
In Windows it is =CHAR(10)
In Mac's I believe it is =CHAR(13)

MIKE

http://www.skeptic.com/


Report •

#2
April 10, 2011 at 17:34:40
Hi Mike,
Thanks a lot for answering and for your advise about my e-mail address (unfortunately, I can't find the "Edit" button... I'll go on searching).
Unfortunately too, the search & replace tool doesn't accept the key sequence.
I also tried with =CHAR(10) and =CHAR(13), and it just inserts those texts, not the line break.
Thanks!
E.

PS: I am editing this 2nd message just because I can. I mean, I do see the Edit button here, but curiously, I can't see it in my 1st message.


Report •

#3
April 10, 2011 at 18:24:31
You can take Mike's idea and macrofy it. This presumes you have Selected the range you want to alter. I'm in Excel 2003/Windows:

Sub BreakLines()
For Each c In ActiveCell.CurrentRegion.Cells
c.Value = Application.WorksheetFunction.Substitute(c, "|", Chr(10))
Next
End Sub


Report •

Related Solutions

#4
April 10, 2011 at 19:13:00
I believe that Excel for Mac only lets you use Macros that are written in Applescript, so here is a formula solution.

With your data in cell A1 put this formula in cell B1:

=SUBSTITUTE(A1," | ",CHAR(10))

Don't forget to format the cell for Word Wrap, else you will see a funny looking box figure where the CHAR(10) is located.

On the Mac side, replace the CHAR(10) with CHAR(13)

MIKE

http://www.skeptic.com/


Report •

#5
April 10, 2011 at 19:27:13
Mike, that's even better. I like the formulas over macros when they are possible.

p.


Report •

#6
April 10, 2011 at 19:35:43
Paul, since I know just over nil about macros,
I rely on formula, even the convoluted and ugly ones.

MIKE

http://www.skeptic.com/


Report •

#7
April 10, 2011 at 20:48:58
It NEARLY worked!

My Excel is in Spanish, so I had to write "SUSTITUIR" instead of "SUBSTITUTE".
Maybe for the same reason, I have to use semicolons instead of commas.

The cell I want to modify is C185, whose text is:
...les archéologues|et d'autres autorités.

In cell D185, I wrote:
=SUSTITUIR(C185;"|";"#")
And I got:
...les archéologues#et d'autres autorités.

But if I write:
=SUSTITUIR(C185;"| ";CHAR(13))
I get:
#¿NOMBRE?
(In case you don't know, "NOMBRE" is Spanish for "NAME").

I thought maybe I should use the Spanish version of CHAR(13), does that make sense?
Or CHAR(13) should work for all languages?

Thanks again to both!


Report •

#8
April 10, 2011 at 20:55:40
✔ Best Answer
FOUND IT!
Indeed, I had to put the Spanish expression:

=SUSTITUIR(C185;"|";CARACTER(13))

I hope this helps future visitors.

Gracias amigos!! I didn't even know the "SUBSTITUTE/SUSTITUIR" function.


Report •

#9
April 11, 2011 at 03:31:34
Me alegro de que podía ayudar a.

(Thanks to Google translate for the above.)

MIKE

http://www.skeptic.com/


Report •

#10
April 11, 2011 at 06:00:14
Well, the very least I can do is to help you back with my native language!

Me alegro de haber podido ayudarte.

:)


Report •

#11
May 11, 2011 at 12:58:19
I just want to add that the cells must be in the "Adjust text" format.
(or however it is called in English versions of Excel; in Spanish it's "Ajustar texto").

Report •


Ask Question