Solved

Remove line break in excel with mac

Posted on 2014-04-08
44
7,083 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
  • 19
  • 17
  • 2
  • +3
44 Comments
 
LVL 48

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 48

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
 

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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 27

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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Accepted Solution

by:
Rob Jurd, EE MVE 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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

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

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now