How to remove line shift

%3CLINGO-SUB%20id%3D%22lingo-sub-2702514%22%20slang%3D%22en-US%22%3EHow%20to%20remove%20line%20shift%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2702514%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20remove%20all%20line%20shifts%20in%20an%20Excel%20spreadsheet%3F%3C%2FP%3E%3CP%3EYou%20can%20insert%20a%20line%20shift%20by%20pressing%20Alt%2BEnter%2C%20but%20how%20do%20I%20find%20these%3F%3C%2FP%3E%3CP%3EI'm%20using%20Excel%20365.%20In%20previous%20versions%20I%20could%20past%20the%20line%20shift%20symbol%20(%C2%B6)%20in%20the%20find%20box%20and%20replace%20all%20instances.%20This%20is%20no%20longer%20working.%20The%20special%20character%20choices%20are%20also%20gone.%3C%2FP%3E%3CP%3EThe%20user%20case%20is%20to%20import%20all%20sort%20of%20price%20lists%20from%20different%20suppliers%20and%20generate%20.csv%20files%20for%20export%20to%20our%20CRM%20software.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2702514%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2702598%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20line%20shift%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2702598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142288%22%20target%3D%22_blank%22%3E%40UpNorth78%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUBSTITUTE(SUBSTITUTE(B2%2CCHAR(13)%2C%22%22)%2CCHAR(10)%2C%22%22)%3CBR%20%2F%3EIf%20you%20prefer%20VBA%20you%20can%20do%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20RemoveCarriageReturns()%0ADim%20MyRange%20As%20Range%0AApplication.ScreenUpdating%20%3D%20False%0AApplication.Calculation%20%3D%20xlCalculationManual%0A%0AFor%20Each%20MyRange%20In%20ActiveSheet.UsedRange%0AIf%200%20%26lt%3B%20InStr(MyRange%2C%20Chr(10))%20Then%0AMyRange%20%3D%20Replace(MyRange%2C%20Chr(10)%2C%20%22%22)%0AEnd%20If%0ANext%0A%0AApplication.ScreenUpdating%20%3D%20True%0AApplication.Calculation%20%3D%20xlCalculationAutomatic%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

2 Replies

@UpNorth78 

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

 

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