Removing &#x0D from cell values

Dickiesplitz

New Member
Joined
Apr 29, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
How do they appear in the cell? Front, middle, end? Please share some examples.
 
Upvote 0
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. 

 
Upvote 0
I think you can simply use find and replace to get rid of these unwanted characters
 
Upvote 0
Maybe.
PS: I think that is meant to represent a carriage return
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top