Removing space characters

[Andrew@Telstra]

To Whom it may concern,

I am using Excel 2003 to create reports from ISPF *.csv files. Due to the
type of dataset I'm using, many of the cells will contain space characters
that I will want to delete. For the majority of the report, a simple
"Replace" code will work well, but there are some cells that need only the
space characters on the left removed. Also, the length of data that needs
to be reported varies dramatically. So, how can I format the text in a cell
so that all of the spaces within the text remain, and those on the left of
the last character are removed?


TIA
Andrew from Telstra

 

[Rick Rothstein \(MVP - VB\)]

I am using Excel 2003 to create reports from ISPF *.csv files. Due to the

type of dataset I'm using, many of the cells will contain space characters
that I will want to delete. For the majority of the report, a simple
"Replace" code will work well, but there are some cells that need only the
space characters on the left removed. Also, the length of data that needs
to be reported varies dramatically. So, how can I format the text in a
cell so that all of the spaces within the text remain, and those on the
left of the last character are removed?

Since you asked in the programming newsgroup, I assume you want a VBA
answer... look up the LTrim function in the help files.

Rick

 

[Jim Cone]

re: "spaces within the text remain, and those on the left of
the last character are removed"

Sub RoundEmUp()
'Jim Cone - San Francisco - July 2007
Dim strPart As String
Dim strText As String

strText = Range("B5").Text
strPart = Right$(strText, 2)

Do While Asc(strPart) = 32
strText = Left$(strText, Len(strText) - 2)
strPart = Right$(strPart, 1)
strText = strText & strPart
strPart = Right$(strText, 2)
Loop
Range("B5").Value = strText
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Andrew@Telstra"
<[email protected]>
wrote in message
To Whom it may concern,

I am using Excel 2003 to create reports from ISPF *.csv files. Due to the
type of dataset I'm using, many of the cells will contain space characters
that I will want to delete. For the majority of the report, a simple
"Replace" code will work well, but there are some cells that need only the
space characters on the left removed. Also, the length of data that needs
to be reported varies dramatically. So, how can I format the text in a cell
so that all of the spaces within the text remain, and those on the left of
the last character are removed?
TIA
Andrew from Telstra