Remove Spaces (modify a current macro)

Hello, I have a macro I use to remove the word "USD" from columns E and F. I need to add the following to the macro code: TRIM(SUBSTITUTE(this needs to be column E and F,CHAR(160),"")) this will...

excel

HansVogelaar

Feb 21, 2022

Sub RemoveUSDText()
'
' RemoveUSDText Macro
' Col E and F
    With Range("E:F")
        .Replace What:="USD", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        .Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    End With
End Sub

Tony2021

HansVogelaar

MVP

Feb 21, 2022

Sub RemoveUSDText()
'
' RemoveUSDText Macro
' Col E and F
    With Range("E:F")
        .Replace What:="USD", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        .Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    End With
End Sub

Tony2021

Iron Contributor

Feb 21, 2022

Perfect! thank you very much. I used the one with the single space chr(160)

Forum Discussion

Remove Spaces (modify a current macro)

Resources