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
965
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,679
Office Version
  1. 2010
Platform
  1. Windows
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"
 

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
965
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,679
Office Version
  1. 2010
Platform
  1. Windows
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?
 

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
965

ADVERTISEMENT

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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,679
Office Version
  1. 2010
Platform
  1. Windows
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
 

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
965
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!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,679
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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
Top