Hi,
I am having trouble trimming a trailing space from a string that I have extracted from a cell as part of a macro.
I have looked at different options and the one that seems to be the best is the Trim function, in particular, the RTrim. Here is the code that I have written
Code:
Sheets("Projected Time").Select ' selects the correct worksheet
CellRange = ("G" & RangeNum) ' This is part of a loop so the RangeNum var changes with each iteration
Range(CellRange).Select ' Selects the cell where I want the text from
TrackAcc = ActiveCell.Value 'Assigns the contents to the variable TrackAcc
MsgBox (TrackAcc & ".") 'This has been to allow me to test the value while debugging
TrackAcc = RTrim(TrackAcc) 'This should trim any trailing spaces
MsgBox (TrackAcc & ".") 'This has been to allow me to test the value while debugging
The string values in the cells very in length from 2 to 4 chars (eg EF or ABDC) but always have 1 space at the end. In some cases there are spaces inside the string value (eg ST F or E F) and I need to keep those spaces.
When I run the above code, for the first messagebox I might get "BALT ." then the second message box appears with "BALT ." (exactly the same).
Anyone got any suggestions as to how I can do this?
Thanks
Simon
The following code loops through each cell in "D", but does not trim cells w/ text that has leading spaces, why?
Code:
Sub TrimRecordNames()
Dim Wss As Worksheet
Dim i As Long
Dim LRow As Long
Set Wss = ActiveSheet
LRow = Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To LRow
WorksheetFunction.Trim (Wss.Cells(i, 4))
Next i
End Sub
Thank you
Doug
What is the correct way of using TRIM function on a range of cells in a given column?
I am trying to use TRIM function to remove spaces on data in a given column. When i copy the formula on the first row and do a paste special - formulas on the cells below, it ends up pasting the value instead of trimming the value in the next row.
Hello,
I have a workbook with multiple columns of data which I would like to run the trim function on. Is there anyway to run a trim formula on every active cell on a sheet? Thanks!
Hi,
I have a spreadsheet (Excel 2003 SP2) with 12 columns and 63,722 rows (Imported from crystal reports). Every cell contains information. and I need to be adle to sort it but extra spaces in about 1/3 of the cells prevents this. At the moment I'm going through this manually getting rid of the extra spaces but it takes so long to do that a new report comes out before the last has been "trimmed/cleaned". I have found the trim function but must be making a hash of it as I can only remove the extra spaces from one cell at a time whereas I would prefer to remove them from every cell. Can you please help?
I have a set of cells from which I need to individually pull the information before, between, and after a certain set of characters. Those characters are my choice, so I was thinking about "***" and "---".
So the cell would say something like this "Before***Between---After" I could also do "Before***Between***After" (whatever is easiest to work with). I would like to pull the information represented in the examlpe by "Before" into one cell, by "Between" in a second cell, and by "After" in a third.
Thank you,
Justin
I have a file that has varying number of spaces before a string lead by 5 numbers and I can't get the trim function to get rid of the leading spaces, Any ideas?
Here is an example:
40000 Fees Earned On TTandL (12 leading spaces)
is there a trim function for numbers?
i downloaded some data and has leading spaces. trim function does not work, how can i get rid of the spaces with numbers in the cell?
thank you.
Hi guys
i'm looking for a vba code that i can use to replace the trim function in excel and yet still get the same results as when i use the trim function. it's more like writing a program which returns a text value with the leading and trailing spaces removed.
i'm using office 2007 on a win7 system
thanks in advance
