Aug 31 2021 05:39 AM
How can I remove all line shifts in an Excel spreadsheet?
You can insert a line shift by pressing Alt+Enter, but how do I find these?
I'm using Excel 365. In previous versions I could past the line shift symbol (¶) in the find box and replace all instances. This is no longer working. The special character choices are also gone.
The user case is to import all sort of price lists from different suppliers and generate .csv files for export to our CRM software.
Aug 31 2021 06:01 AM - edited Aug 31 2021 06:01 AM
You can use a formula
=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")
Or VBA
Sub RemoveCarriageReturns()
Dim MyRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each MyRange In ActiveSheet.UsedRange
If 0 < InStr(MyRange, Chr(10)) Then
MyRange = Replace(MyRange, Chr(10), "")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sep 02 2021 02:00 AM
Thank you for your useful input, @Juliano-Petrukio.
I ended up using the CLEAN function removing all ASCII characters from 1 to 32 (all the original non-printing characters).