Applying trim and substitute formula to specified cell

[shreeram.maroo]

Hi I want to apply the following formula to selected cells in excel and then paste special the selected cells at the same place

TRIM(SUBSTITUTE(F6,CHAR(160),CHAR(32)))

F6 is just a location of specific cell, i need to apply it to selected cells. Can anyone help me with the macro for this.

Regards
Shreeram

[HansV]

Do you want to enter that exact formula in all selected cells? Or should F6 change depending on the cell?

[shreeram.maroo]

F6 should change depending on cell

[snb]

Code: Select all

Sub M_snb()
   for each it in selection
     it=trim(replace(it,chr(160)," "))
   next
End Sub

NB. VBA.Trim() <> application.trim()

[HansV]

So perhaps

Code: Select all

Sub M_snb()
    Dim it As Range
    Application.ScreenUpdating = False
     For Each it In Selection
         it.Value = Application.Trim(Replace(it.Value, Chr(160), " "))
     Next it
    Application.ScreenUpdating = True
End Sub

[DocAElstein]

Hello
The Pretty way, ( and a Sub to check it: You will need the function in the uploaded file for that Check Sub)

Code: Select all

Option Explicit
Sub TrimSubstituteItBeutifully()  '  https://eileenslounge.com/viewtopic.php?f=27&t=39185
 Let Selection.Value = Evaluate("=IF({1},TRIM(SUBSTITUTE(" & Selection.Address & ",CHAR(160),CHAR(32))))")
End Sub








Sub CheckIt()
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1)
 
 Let Ws1.Range("A1").Value = "  " & Chr(160) & " a "
 Let Ws1.Range("A2").Value = "  " & Chr(160) & " b "
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Ws1.Range("A1").Value)
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Ws1.Range("A2").Value)
 
 Ws1.Activate
 Ws1.Range("A1:A2").Select

 Let Selection.Value = Evaluate("=IF({1},TRIM(SUBSTITUTE(" & Selection.Address & ",CHAR(160),CHAR(32))))")  '  https://eileenslounge.com/viewtopic.php?f=27&t=39185

 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Ws1.Range("A1").Value)
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Ws1.Range("A2").Value)
End Sub

Alan

[rory]

Just note that that, unlike the loop approach, will only work if your selection is a contiguous block of cells.

[HansV]

The Pretty way

Beauty is in the eye of the beholder...

[DocAElstein]

Behold.... I see It ! :)
( https://eileenslounge.com/viewtopic.php ... 80#p279880 )

[DocAElstein]

Just note that that, unlike the loop approach, will only work if your selection is a contiguous block of cells.

Ahh, good point/catch...
I geuss one way over that would be to loop the Areas, (depending on how big the Areas are would determine if it was worthwhile to do it the pretty way or just loop it all and be done with it)

Code: Select all

Sub TrimSubstituteItBeutifullyInAreas()  '  https://eileenslounge.com/viewtopic.php?p=303489#p303489
Dim StearIt As Variant
    For Each StearIt In Selection.Areas
     Let StearIt.Value = Evaluate("=IF({1},TRIM(SUBSTITUTE(" & StearIt.Address & ",CHAR(160),CHAR(32))))")
    Next StearIt
End Sub

[shreeram.maroo]

Thanks guys.

[snb]

@doc

Code: Select all

Sub M_snb()
  Selection.Name = "snb"
  [snb].Replace Chr(160), " "
  [snb] = [index(trim(snb),)]
End Sub