Remove only one space, if there is a space, from the end of text.

[pglufkin]

Was wondering if someone could help me.

Was thinking something along the lines of the procedure below, but I only want to remove one space at the end of my text within a cell, if there is a space. Any quick ideas? Thx.

Sub hth() 
    Dim c As  Range 
     
    For Each c In Range("H1", Range("H" & Rows.Count).End(xlUp)) 
        c.Value = Trim(c.Value) 
    Next c 
End Sub

 

[ZAX]

Hi,
Here's THe Solution:

Sub hth()
    Dim c As Range
     
    For Each c In Range("H1", Range("H" & ROWS.Count).End(xlUp))
        i = c.Value
        i = Left(i, Len(i) - InStrRev(i, " ")) & Right(i, Len(i) - (InStrRev(i, " ") - 1))
    Next c
End Sub

ZAX

 

[pglufkin]

Thx, however your procedure does not write anything to c.value, doesn't change my cell with the needed change.

 

[pglufkin]

Your procedure produces a blank where there was once text . . . i had to add a couple of lines. Can anyone else help me?

 

[Marcelo Branco]

Maybe

Sub hth()
    Dim c As Range
     
    For Each c In Range("H1", Range("H" & Rows.Count).End(xlUp))
        If Right(c.Value, 1) = " " Then
            c.Value = StrReverse(Mid(StrReverse(c.Value), 2))
        End If
    Next c
End Sub

M.

 

[pglufkin]

Bingo, thank you

 

[Marcelo Branco]

Bingo, thank you

You are welcome!

Glad for helping


M.

 

[Rick Rothstein]

Bingo, thank you

I think this non-looping macro will also do what you wanted...

Sub RemoveOneSpaceFromEndOnly()
  Dim Addr As String
  Addr = "H1:H" & Cells(Rows.Count, "H").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(RIGHT(@)="" "",LEFT(@,LEN(@)-1),@)", "@", Addr))
End Sub

 

[Marcelo Branco]

Rick,

Just curious. What is the role of @ in your formula. New for me.
It "represents" the value of each cell in the range?

M.

 

[Rick Rothstein]

Rick,

Just curious. What is the role of @ in your formula. New for me.
It "represents" the value of each cell in the range?

It is simply a "stand-in" character for the address (you can use any character that will not be included in the argument's text) that gets replaced all at once by the Replace function... it avoids having to concatenate the address into the argument's text string over and over again which makes constructing/reading the argument a little easier.