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....
thuna72Asked:
Who is Participating?
 
RobConnect With a Mentor Owner (Aidellio)Commented:
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
 
Rgonzo1971Commented:
Hi,

Maybe by Typing in Find  
[Alt] + 010
and

Nothing in Replace

Regards
0
 
thuna72Author Commented:
hi

thanks, but unfortunately that's the windows-way to do it, won't work with MAC....
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Rgonzo1971Commented:
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
 
thuna72Author Commented:
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
 
RobOwner (Aidellio)Commented:
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
 
thuna72Author Commented:
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
 
RobOwner (Aidellio)Commented:
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
 
thuna72Author Commented:
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
 
RobOwner (Aidellio)Commented:
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
 
thuna72Author Commented:
there it is...
0
 
thuna72Author Commented:
0
 
RobOwner (Aidellio)Commented:
*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
 
thuna72Author Commented:
ugh, ok :) so can i use the macro as you have it here or try the lines one by one?
0
 
thuna72Author Commented:
WAIT! 13 worked! great!! now how do i extend that to my entire worksheet?
0
 
RobOwner (Aidellio)Commented:
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
 
RobOwner (Aidellio)Commented:
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
 
thuna72Author Commented:
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
 
RobOwner (Aidellio)Commented:
... 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
 
thuna72Author Commented:
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
 
RobOwner (Aidellio)Commented:
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
 
thuna72Author Commented:
...now it crashed.
0
 
thuna72Author Commented:
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
 
serialbandCommented:
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
 
RobOwner (Aidellio)Commented:
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
 
RobOwner (Aidellio)Commented:
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
 
thuna72Author Commented:
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
 
RobOwner (Aidellio)Commented:
Sure... attach and i'll see what it does for me... that way we'll know if it's a Mac / Win issue
0
 
thuna72Author Commented:
0
 
thuna72Author Commented:
ha, with that last code it worked!
0
 
RobOwner (Aidellio)Commented:
Ok! so now just increase the rows and columns to encapsulate the dataset you've got.
0
 
thuna72Author Commented:
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
 
RobOwner (Aidellio)Commented:
ok in that sample book you provided i just did 100 rows and 20 columns and it ran fine
0
 
RobOwner (Aidellio)Commented:
wooo hoo!  *laughing*
0
 
thuna72Author Commented:
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
 
thuna72Author Commented:
great and patient help to an absolute macro beginner, THANK YOU!
0
 
RobOwner (Aidellio)Commented:
My pleasure - I can finally go to bed now (midnight here) :D
0
 
thuna72Author Commented:
Well, good nite then...
0
 
Chris LeedsOwnerCommented:
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
 
RobOwner (Aidellio)Commented:
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
 
Chris LeedsOwnerCommented:
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
 
Stanislav AnvCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.