VBA for - If the last character in a cell is a space, delete it. Do this for a whole column except row 1

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
1,027
Office Version
  1. 365
Platform
  1. Windows
Hello all,

After 1 hour of frustrating searching, I gave up and decided to go to the experts. I got every answer known to mankind except the one that I need.

In column B, I have several rows of data. They can be any combination and in any order of numbers, letters, dashes and spaces.

For example,

I might have 125-2D3 , but it has a space at the end, so it's actually 125-2D3"space"

I could also have D25 429T"space" (note that there is also a space between the "5" and the "4", but I don't want that deleted), only the space at the end.

I need VBA code that only removes any space after the last non-space character, but I need to apply it to an entire column (except row 1)

I suppose you could create the code to delete all spaces after the last non-space character, but I think I will be ok with it only searching for one space.

Thanks much
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
VBA has a function named RTrim which will trim spaces from the right side of text. So, if you have this...
VBA Code:
Txt = "   text    "
doing this...
VBA Code:
Txt = "   text    "
Txt = RTrim(Txt)
will make Txt contain
VBA Code:
 "   text"
 
Upvote 0
Hello Rick and thanks for the response. How do I tell column B to remove the right-most space from each cell without leaving a formula behind if there is one? Is there code for that?

Thanks much
 
Upvote 0
I'm not 100% sure I understand. Are you saying you have a formula in the cells that is putting text into your cells and that text has a trailing space? Or are you asking how to convert any formulas into constants and if that constant has a trailing space, remove it?
 
Upvote 0
If there is a space, simply delete it. No resulting formula. I presently have no formula in the column and want to keep it that way. I have text that has a trailing space. I need VBA code to do this.
In other words
Select column B
if the last character in each cell in column B is a space, delete it.

That's it

Thanks
 
Upvote 0
Give this macro a try....
VBA Code:
Sub RightTrimCellsInColumnB()
  Dim LR As Long
  LR = Cells(Rows.Count, "B").End(xlUp).Row
  Range("B2:B" & LR) = Evaluate(Replace("IF(B2:B#="""","""",LEFT(B2:B#,FIND(""|"",SUBSTITUTE(B2:B#,RIGHT(TRIM(B2:B#)),""|"",LEN(B2:B#)-LEN(SUBSTITUTE(B2:B#,RIGHT(TRIM(B2:B#)),""""))))))", "#", LR))
End Sub
 
Upvote 0
Rick, that worked absolutely perfectly!
I am amazed that so much code (relatively speaking) is required for what is seemingly such a simple action to me, but you know this stuff WAY better than me, so I will just continue graciously and humbly learning.

Thanks very much!
 
Upvote 0
The solution I gave you used no loops, so it was a little on the longish side. Here is a looping version which is shorter but I think (untested) slower...
VBA Code:
Sub RightTrimCellsInColumnB()
  Dim Cell As Range
  For Each Cell In Range("B1", Cells(Rows.Count, "B").End(xlUp))
    Cell.Value = RTrim(Cell.Value)
  Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top