2

I'm trying to do a find and replace in Excel 2016 for Mac, to replace all line breaks with a space.

On Windows I'd do CTRL+R for find and replace, then hold the alt key and type 010 which would insert a linebreak into the find field.

I'd then type a Space, and it hit Replace All.

I can't seem to workout how to do the keyboard equivalent on a Mac?

Any suggestions.

JayRizzo
  • 256
  • 3
  • 11
n8udd
  • 180
  • https://apple.stackexchange.com/questions/84609/how-to-add-a-line-break-in-a-cell-in-excel-for-mac#84610 Might work I’d you do those combinations in the find and replace dialogue – dwightk Aug 21 '18 at 11:37
  • I saw the link you sent, and it works when editing text in a cell, but not for the find and replace. It simply searches for spaces, rather than carriage returns. – n8udd Aug 21 '18 at 11:40
  • Perhaps you should consider a different editing program, I have to edit ".CSV" files and replace one type of line break with another - would not consider excel for that though even though it shows a couple of csv formats when saving... – Solar Mike Aug 21 '18 at 11:44
  • I can do that as an option, and hopefully use regex. It's just inconvenient considering I can do it in less than 10 keys on Windows. – n8udd Aug 21 '18 at 11:46
  • I'm happy with the other benefits of my mac compared to a few keypresses – Solar Mike Aug 21 '18 at 11:55
  • Are you importing data from a CSV (or similar) that has these LF chars? – Allan Aug 21 '18 at 12:11
  • I've been supplied with the .xlsx file, and I need to tidy it up before it is imported into a MySQL database. – n8udd Aug 21 '18 at 12:12
  • My suggestion is to convert to a CSV, run through sed to replace the LF with a space, then either import directly to MySQL or back to Excel. If you're using the MySQL plugin for Excel (windows), great. If not, you'll run into import issues and have to run it through sed anyway. (Done this several times) – Allan Aug 21 '18 at 12:25
  • Have you tried the MS forum? https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac-mso_mac2016?sort=LastReplyDate&dir=Desc&tab=All&status=all&mod=&modAge=&advFil=&postedAfter=&postedBefore=&threadType=all&isFilterExpanded=false&page=1 – Tom Gewecke Aug 21 '18 at 12:25

1 Answers1

2

In most macOS applications ControlEnter works, and in Excel ControlAltEnter works in cells, and even in Find and Replace it will enter a space, but it doesn't appear to search.

Similarly \n which is an escaped new line doesn't work, nor does copying and pasting a new line character from another application.

In word ^p works as an escape character but again not in Excel.

It looks very much like this isn't possible in Excel for Mac. The best thing I could suggest at this point is to ask for Microsoft to fix this via their feedback forum:

https://excel.uservoice.com/

The last option I was going to suggest was using Numbers which is the Apple version of Excel, it works very well in general but has the same limitation from my testing.

Last option would be to use Excel for Windows via a Remote Desktop application like Citrix, a simulator like WINE or a virtual machine like VirtualBox, Parallels or VMWare.

unknowndomain
  • 4,192