SOLVED

Remove Spaces (modify a current macro)

%3CLINGO-SUB%20id%3D%22lingo-sub-3193152%22%20slang%3D%22en-US%22%3ERemove%20Spaces%20(modify%20a%20current%20macro)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3193152%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20a%20macro%20I%20use%20to%20remove%20the%20word%20%22USD%22%20from%20columns%20E%20and%20F.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20add%20the%20following%20to%20the%20macro%20code%3A%3C%2FP%3E%3CP%3ETRIM(SUBSTITUTE(%3CSTRONG%3Ethis%20needs%20to%20be%20column%20E%20and%20F%3C%2FSTRONG%3E%2CCHAR(160)%2C%22%22))%3C%2FP%3E%3CP%3Ethis%20will%20remove%20all%20spaces%20in%20the%20cells.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20apply%20it%20to%20columns%20E%20and%20F%20only.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20could%20I%20do%20this%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20RemoveUSDText()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20RemoveUSDText%20Macro%3CBR%20%2F%3E'%20Col%20E%20and%20F%3CBR%20%2F%3E%3CBR%20%2F%3EColumns(%22E%3AF%22).Select%3CBR%20%2F%3ESelection.Replace%20What%3A%3D%22USD%22%2C%20Replacement%3A%3D%22%22%2C%20LookAt%3A%3DxlPart%2C%20_%3CBR%20%2F%3ESearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse%2C%20_%3CBR%20%2F%3EReplaceFormat%3A%3DFalse%2C%20FormulaVersion%3A%3DxlReplaceFormula2%3CBR%20%2F%3ERange(%22E1%22).Select%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3193152%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3193202%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Spaces%20(modify%20a%20current%20macro)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3193202%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20RemoveUSDText()%0A'%0A'%20RemoveUSDText%20Macro%0A'%20Col%20E%20and%20F%0A%20%20%20%20With%20Range(%22E%3AF%22).Select%0A%20%20%20%20%20%20%20%20.Replace%20What%3A%3D%22USD%22%2C%20Replacement%3A%3D%22%22%2C%20LookAt%3A%3DxlPart%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20SearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20ReplaceFormat%3A%3DFalse%2C%20FormulaVersion%3A%3DxlReplaceFormula2%0A%20%20%20%20%20%20%20%20.Replace%20What%3A%3DChr(160)%2C%20Replacement%3A%3D%22%22%2C%20LookAt%3A%3DxlPart%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20SearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20ReplaceFormat%3A%3DFalse%2C%20FormulaVersion%3A%3DxlReplaceFormula2%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Super Contributor

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

3 Replies
best response confirmed by Tony2021 (Super Contributor)
Solution

 

 

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 

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
Perfect! thank you very much. I used the one with the single space chr(160)