Posted to microsoft.public.excel.programming
|
|
Removing commas
Thanks.
"Rob Bovey" wrote in message ...
Hi Ray,
For the special case of commas only, something like this would be much
faster:
Function szReplaceCommas() As String
Dim szToProcess As String
szToProcess = ",,,,,206,,,,210,,,,,,217,,,,,222,,,,,,,229,,,233, ,,,,"
''' Replace all commas with spaces
szToProcess = Replace(szToProcess, ",", " ")
''' Trim out extra spaces
szToProcess = Application.WorksheetFunction.Trim(szToProcess)
''' Replace spaces with commas and add back trailing comma
szToProcess = Replace(szToProcess, " ", ",") & ","
szReplaceCommas = szToProcess
End Function
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"rcmiv" wrote in message
om...
I have this string:
,,,,,206,,,,210,,,,,,217,,,,,222,,,,,,,229,,,233,, ,,,
I would like to output this string:
206,210,217,222,229,233,
How can this be done in VBA?
Reviewing the group, it seems like this snip of code could get me
close, but I am unclear on how to adapt it to my purpose.
-----
Function stripped$(s$)
Const ok = "[#A-z]" '<remove the space here
Dim i%, t$, r$
For i = 1 To Len(s)
t = Mid(s, i, 1)
r = r & iif(t Like ok,t," ")
Next
'optional remove double spaces
'it has to be the worksheetfunction
'vba trim just trims on the outside
r=application.worksheetfunction.trim(r)
stripped = r
End Function
off the cuff.. hope it works :)
keepITcool
-------------
Thanks,
Ray
|