Removing commas

[rcmiv]

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

 

[Rob Bovey]

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 *

 

[Tom Ogilvy]

sStr = ",,,,,206,,,,210,,,,,,217,,,,,222,,,,,,,229,,,233,,,,,"
sStr = Application.Substitute(sStr,","," ")
sStr = application.Trim(sStr)
sStr = Application.Substitute(sStr," ",",")
? sStr
206,210,217,222,229,233

 

[rcmiv]

Thanks.

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 *

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

 

[rcmiv]

Thanks.

sStr = ",,,,,206,,,,210,,,,,,217,,,,,222,,,,,,,229,,,233,,,,,"
sStr = Application.Substitute(sStr,","," ")
sStr = application.Trim(sStr)
sStr = Application.Substitute(sStr," ",",")
? sStr
206,210,217,222,229,233

--
Regards,
Tom Ogilvy

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