Link to home
Start Free TrialLog in
Avatar of thuna72
thuna72

asked on

Remove line break in excel with mac

Does anyone know a SIMPLE way of finding and removing/replacing line breaks in excel (MAC VERSION)? Would be very grateful....
Avatar of Rgonzo1971
Rgonzo1971

Hi,

Maybe by Typing in Find  
[Alt] + 010
and

Nothing in Replace

Regards
Avatar of thuna72

ASKER

hi

thanks, but unfortunately that's the windows-way to do it, won't work with MAC....
Avatar of thuna72

ASKER

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....
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

Avatar of thuna72

ASKER

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!
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.
Avatar of thuna72

ASKER

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
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?
Avatar of thuna72

ASKER

there it is...
*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

Avatar of thuna72

ASKER

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

ASKER

WAIT! 13 worked! great!! now how do i extend that to my entire worksheet?
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.
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

Avatar of thuna72

ASKER

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...
... 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?
Avatar of thuna72

ASKER

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....
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

Avatar of thuna72

ASKER

...now it crashed.
Avatar of thuna72

ASKER

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....
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
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
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

Avatar of thuna72

ASKER

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?
Sure... attach and i'll see what it does for me... that way we'll know if it's a Mac / Win issue
ASKER CERTIFIED SOLUTION
Avatar of Rob
Rob
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thuna72

ASKER

ha, with that last code it worked!
Ok! so now just increase the rows and columns to encapsulate the dataset you've got.
Avatar of thuna72

ASKER

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!
ok in that sample book you provided i just did 100 rows and 20 columns and it ran fine
wooo hoo!  *laughing*
Avatar of thuna72

ASKER

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!
Avatar of thuna72

ASKER

great and patient help to an absolute macro beginner, THANK YOU!
My pleasure - I can finally go to bed now (midnight here) :D
Avatar of thuna72

ASKER

Well, good nite then...
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
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
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.
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")