Applying trim and substitute formula to specified cell
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Applying trim and substitute formula to specified cell
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
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
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Applying trim and substitute formula to specified cell
Do you want to enter that exact formula in all selected cells? Or should F6 change depending on the cell?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Applying trim and substitute formula to specified cell
F6 should change depending on cell
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Applying trim and substitute formula to specified cell
Code: Select all
Sub M_snb()
for each it in selection
it=trim(replace(it,chr(160)," "))
next
End Sub
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Applying trim and substitute formula to specified cell
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 621
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Applying trim and substitute formula to specified cell
Hello
The Pretty way, ( and a Sub to check it: You will need the function in the uploaded file for that Check Sub)
Alan
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
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Applying trim and substitute formula to specified cell
Just note that that, unlike the loop approach, will only work if your selection is a contiguous block of cells.
Last edited by rory on 19 Jan 2023, 11:47, edited 1 time in total.
Regards,
Rory
Rory
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Applying trim and substitute formula to specified cell
Beauty is in the eye of the beholder...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 621
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Applying trim and substitute formula to specified cell
Behold.... I see It ! :)
( https://eileenslounge.com/viewtopic.php ... 80#p279880 )
( https://eileenslounge.com/viewtopic.php ... 80#p279880 )
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 5StarLounger
- Posts: 621
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Applying trim and substitute formula to specified cell
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
Last edited by DocAElstein on 21 Jan 2023, 06:32, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
if you like to evaluate
@doc
Code: Select all
Sub M_snb()
Selection.Name = "snb"
[snb].Replace Chr(160), " "
[snb] = [index(trim(snb),)]
End Sub