Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Feb 21, 2022
Solved

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 remove all spaces in the cells. 

I need to apply it to columns E and F only.  

How could I do this?  

 

Sub RemoveUSDText()
'
' RemoveUSDText Macro
' Col E and F

Columns("E:F").Select
Selection.Replace What:="USD", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Range("E1").Select
End Sub

excel
  • HansVogelaar's avatar
    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 

3 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 21, 2022

    Tony2021 

    And if you want to remove ALL spaces:

    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
        .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    End With
End Sub
  • HansVogelaar's avatar
    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 

    • Tony2021's avatar
      Tony2021
      Iron Contributor
      Feb 21, 2022
      Perfect! thank you very much. I used the one with the single space chr(160)

Resources