Solved

Remove line break in excel with mac

Posted on 2014-04-08
44
8,254 Views
Last Modified: 2016-04-10
Does anyone know a SIMPLE way of finding and removing/replacing line breaks in excel (MAC VERSION)? Would be very grateful....
0
Comment
Question by:thuna72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 17
  • 2
  • +3
44 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39986695
Hi,

Maybe by Typing in Find  
[Alt] + 010
and

Nothing in Replace

Regards
0
 

Author Comment

by:thuna72
ID: 39986700
hi

thanks, but unfortunately that's the windows-way to do it, won't work with MAC....
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39986709
Hi,

here they propose this formula

=SUBSTITUTE(A1,CHAR(13),"")

Open in new window


http://answers.microsoft.com/en-us/mac/forum/macoffice2008-macexcel/how-can-i-findreplace-line-breaks-in-mac-2008/fc983c79-7a62-4bdc-8dcc-0b1da2d80bb1

Regards
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thuna72
ID: 39986724
hi, thanks, i had also tried that and it didn't work because it didn't automatically update the line numbers in the formula. also, it's a very work-intensive approach in my case since my table is huge and the formula needs to be applied to every column separately. it would be GREAT if there were another way, a way in which line breaks could be found in the "Find"-field....
0
 
LVL 43

Expert Comment

by:Rob
ID: 39990839
Did you consider a macro?  this is a basic example using one cell that contains the ascii 10 line break character.  It then replaces the line break with a space and puts the result back in the cell.  This would be very easy to code further and have it scan a spreadsheet but I wanted to make sure this would work for you on a mac

Sub RemoveBreaks()
    Dim str
    str = Cells(2, 2).Value
    Cells(2, 2).Value = Replace(str, Chr(10), " ")
End Sub

Open in new window

0
 

Author Comment

by:thuna72
ID: 39991165
hi rob
thanks for your answer, it sounds promising but i need some more help since i've never worked with a macro. where exactly do i insert your code?
thanks!
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991180
The easiest way to follow the steps from the office website http://office.microsoft.com/en-au/mac-excel-help/create-run-edit-or-delete-a-macro-HA102927318.aspx#BMxl

Do this in a blank workbook for testing before we move onto your large workbook.

Expand "Create a macro by using Visual Basic for Applications" and do each of the steps (1 to 4)

Once you've done the above steps, go to cell B2 (in the code it's referenced as Cells(2,2)) and enter something like one^two^three (where ^ indicates a new line... do you do Alt+Enter on a mac for that?)

Now go back to the link I posted and expand "Run a Macro" and follow the steps.  You should see a macro called RemoveBreaks if you've copy and pasted my code correctly.  Run it and you should see the line breaks removed if all goes well.
0
 

Author Comment

by:thuna72
ID: 39991331
hi rob

thanks, i did all the above steps and can indeed see the "RemoveBreaks" in the macro, but it does not remove the line breaks if i run it. i attach you a screenshot of the macro i inserted, i just copid it from yours.

on a mac you do ctrl+alt+enter in excel to make a line break. might there be the problem?
macro.tiff
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991402
The code I gave you will only modify the contents of B2.  You've definitely got content in B2?  In fact, a better idea would be to attach the sample workbook if that's ok?
0
 

Author Comment

by:thuna72
ID: 39991437
there it is...
0
 

Author Comment

by:thuna72
ID: 39991442
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991460
*laughing*... that worked for me... now it will be what character code the mac interprets the newline to be.  I've assumed 10 but you should try 13 and combinations thereof.

What I mean is,

Sub RemoveBreaks()
    Dim str
    str = Cells(2, 2).Value
    Cells(2, 2).Value = Replace(str, Chr(10), " ")
    Cells(2, 2).Value = Replace(str, Chr(13), " ")
    Cells(2, 2).Value = Replace(str, Chr(10) & Chr(13), " ")
    Cells(2, 2).Value = Replace(str, Chr(13) & Chr(10), " ")
End Sub

Open in new window

0
 

Author Comment

by:thuna72
ID: 39991467
ugh, ok :) so can i use the macro as you have it here or try the lines one by one?
0
 

Author Comment

by:thuna72
ID: 39991471
WAIT! 13 worked! great!! now how do i extend that to my entire worksheet?
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991526
Yay... I had a feeling....

ok so for your entire worksheet we'll use a loop.  

There are a few ways we can approach this.  If it's just a one off then I would loop over every cell in the sheet doing the replace.  If you have a fixed range, then we can use that too.
If you're going to need to do this more going forward, then I need to know the range that it will operate on.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991540
The simple version that scans 255 columns and 65,535 rows:

Sub RemoveBreaks()
    Dim str
    For i = 1 to 65535
        For j = 1 to 255
            str = Cells(i, j).Value
            Cells(i, j).Value = Replace(str, Chr(13), " ")
        Next j
    Next i
End Sub

Open in new window

0
 

Author Comment

by:thuna72
ID: 39991577
hmm if i open my spreadsheet and run this macro excel goes a bit haywire, respectively it gets stuck. or am i being not patient enough? your indicated number of columns and rows is by far sufficient...
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991585
... yeah it will take a while.  How many columns and rows are we talking here?  and do all columns need to have their contents replaced?
0
 

Author Comment

by:thuna72
ID: 39991593
yes almost all columns need to replace lines and the one i'm testing now is 53 rows and about 12 columns only. it's still working on it....
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991597
What might speed it up a bit is to turn ScreenUpdating off that way Excel isn't trying to update the spreadsheet with every iteration

Sub RemoveBreaks()
    Application.ScreenUpdating = False
    Dim str
    For i = 1 to 65535
        For j = 1 to 255
            str = Cells(i, j).Value
            Cells(i, j).Value = Replace(str, Chr(13), " ")
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:thuna72
ID: 39991598
...now it crashed.
0
 

Author Comment

by:thuna72
ID: 39991603
tried your second suggestion, same result. i do have to click "enable macros" when i open the file, yes? also, i'm working on a mac excel 2011 version, and it tells me i have to downgrade the file to .xls (instead of .xlsx) because obviously excel 2011 does not support excel.

tell me when you're getting tired of this....
0
 
LVL 29

Expert Comment

by:serialband
ID: 39991606
Just FYI
Macs, or Apple, have always used character 13 (CR or Carriage Return) for the newline character.  Unix/Linux have always used character 10 (LF or Line Feed) for the newline.  Doss/Windows have always used  CR + LF for the newline.  If you're tranferring files between OS, you'll need to convert them first or match them all.

https://en.wikipedia.org/wiki/Newline
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991614
Yes you have to click enable macros

you will need to save the spreadsheet as xlsm

excel 2011 should most certainly support excel, it's developed by Microsoft for Mac.

btw, i've run the 53 cols and 12 rows (and vice versa) and it finishes in under a second
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991624
Just to make my code obvious about what is a row and what is a column in the cells()
http://msdn.microsoft.com/en-us/library/office/ff194567(v=office.15).aspx

Sub RemoveBreaks()
    Application.ScreenUpdating = False
    Dim str
    For rw = 1 to 65535
        For cl = 1 to 255
            str = Cells(rw, cl).Value
            Cells(rw, cl).Value = Replace(str, Chr(13), " ")
        Next cl
    Next rw
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:thuna72
ID: 39991635
ok, saved the fiel as xlsm, copied your new code into the editor, ran the code, still the same result: first hayqire then crash. probably no use to attach you my excel file as you work on a pc?
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991648
Sure... attach and i'll see what it does for me... that way we'll know if it's a Mac / Win issue
0
 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 39991649
Sorry I also meant for you to try just this (10 rows and 10 cols)

Sub RemoveBreaks()
    Application.ScreenUpdating = False
    Dim str
    For rw = 1 to 10
        For cl = 1 to 10
            str = Cells(rw, cl).Value
            Cells(rw, cl).Value = Replace(str, Chr(13), " ")
        Next cl
    Next rw
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:thuna72
ID: 39991666
0
 

Author Comment

by:thuna72
ID: 39991671
ha, with that last code it worked!
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991680
Ok! so now just increase the rows and columns to encapsulate the dataset you've got.
0
 

Author Comment

by:thuna72
ID: 39991686
yes, that's what i just did (starting to get to know about macros...), and it worked! it took it about 5 seconds. EXCELLENT! thanks!
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991689
ok in that sample book you provided i just did 100 rows and 20 columns and it ran fine
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991692
wooo hoo!  *laughing*
0
 

Author Comment

by:thuna72
ID: 39991694
best expert forever, thank you, i don't even want to think about how much time this will safe me in the future. have a great day!
0
 

Author Closing Comment

by:thuna72
ID: 39991699
great and patient help to an absolute macro beginner, THANK YOU!
0
 
LVL 43

Expert Comment

by:Rob
ID: 39991716
My pleasure - I can finally go to bed now (midnight here) :D
0
 

Author Comment

by:thuna72
ID: 39991724
Well, good nite then...
0
 
LVL 1

Expert Comment

by:Chris Leeds
ID: 40456662
Robert,

I just read your successful dialog above and got so excited that I signed up for Experts Exchange.

I have a similar, if not the same, situation.

Should I begin here or with a new threat?

Please forgive my parting from protocol, if I am. I'm new here and am ready for direction.

Thank you in advance.

Sincerely,
Chris L
0
 
LVL 43

Expert Comment

by:Rob
ID: 40456870
Hi Chris,
Great news you got so much out of this solution.
Your best bet at this stage is to ask your own question.
Start here: http://support.experts-exchange.com/customer/portal/articles/756544. There is also a link to a "how to" for asking questions. The support site is a wealth of very helpful information that a lot of users do not know exists.
If you need any more help, feel free to message me directly via the EE message system: http:/myMessages.jsp
0
 
LVL 1

Expert Comment

by:Chris Leeds
ID: 40505165
Thanks Rob. I have posted my request for the problem I have that is similar to the one above. I'm just awaiting some responses.
0
 

Expert Comment

by:Stanislav Anv
ID: 41543227
Hello!
What if i have about 170 000 rows?
Sub RemoveBreaks()
    Application.ScreenUpdating = False
    Dim str
    For rw = 1 To 179000
        For cl = 1 To 20
            str = Cells(rw, cl).Value
            Cells(rw, cl).Value = Replace(str, Chr(13), "\n")
        Next cl
    Next rw
    Application.ScreenUpdating = True
End Sub

Open in new window


And i have that error
Microsoft Visual Basic
Run-time error '1004':
Application-defined or object-defined error

When click on Debig
Line is
Cells(rw, cl).Value = Replace(str, Chr(13), "xx")
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We could spend the next millennium discussing the differences of the Mac and Windows platforms. The next century will continue to have fanatics on both side of the equation and neither side will win the war. However, that’s not why we are here. W…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question