Removing &#x0D from cell values

[Dickiesplitz]

I am exporting data from SQL into Excel and see these characters appearing in many cells. Is there a quick way to find and remove all instances using a macro?

 

[alansidman]

In Power Query, employ the Text.Remove function.

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

Learn a cool technique to extract or remove letters, numbers and special characters from strings. Sample workbook to download

www.myonlinetraininghub.com

 

[Cubist]

How do they appear in the cell? Front, middle, end? Please share some examples.

 

[Dickiesplitz]

How do they appear in the cell? Front, middle, end? Please share some examples.

From exported data

Import - May 12 2022 12:00AM - Estimated cost of £8,300 based on 1,000 customers x £8.30 (cost of legal cover premium).


Import - May 12 2022 12:00AM - Business written in last 12 months has been collated and provided to Technical Manager (and will in time be sent to reinsurers). Additional work is being undertaken in understanding current exposure limits. 


 

[Sanjeev1976]

I think you can simply use find and replace to get rid of these unwanted characters

 

[Alex Blakenburg]

Maybe.
PS: I think that is meant to represent a carriage return

Sub ReplaceCharacters()

    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    ' Replace with space after the period
    ws.UsedRange.Replace What:=" 
", replacement:="", MatchCase:=False, LookAt:=xlPart
    ' Replace if there is no space.
    ws.UsedRange.Replace What:="
", replacement:="", MatchCase:=False, LookAt:=xlPart

End Sub