Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commas
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commas
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 "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 |
#4
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commas
Thanks.
"Tom Ogilvy" wrote in message ... 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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing commas from numbers etc. | Excel Worksheet Functions | |||
Removing patterns without removing gridlines | Excel Discussion (Misc queries) | |||
So many commas | Excel Worksheet Functions | |||
tab delimited and commas | Excel Discussion (Misc queries) | |||
Removing commas | Excel Worksheet Functions |