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....
ASKER
hi
thanks, but unfortunately that's the windows-way to do it, won't work with MAC....
thanks, but unfortunately that's the windows-way to do it, won't work with MAC....
Hi,
here they propose this formula
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
here they propose this formula
=SUBSTITUTE(A1,CHAR(13),"")
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
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
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!
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.
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.
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
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?
ASKER
there it is...
ASKER
*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,
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
ASKER
ugh, ok :) so can i use the macro as you have it here or try the lines one by one?
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.
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
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?
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
ASKER
...now it crashed.
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....
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ha, with that last code it worked!
Ok! so now just increase the rows and columns to encapsulate the dataset you've got.
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*
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!
ASKER
great and patient help to an absolute macro beginner, THANK YOU!
My pleasure - I can finally go to bed now (midnight here) :D
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
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
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?
And i have that error
Microsoft Visual Basic
Run-time error '1004':
Application-defined or object-defined error
When click on Debig
Line is
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
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")
Maybe by Typing in Find
[Alt] + 010
and
Nothing in Replace
Regards