Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
I have a subroutine that results in different string lengths sorted in
ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
Maybe something like this as a formula in a worksheet:
=SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ",") MsgBox myStr End Sub VBA's Trim() doesn't do anything with those multiple internal spaces. But application.trim() (the worksheet function), does. owlnevada wrote: I have a subroutine that results in different string lengths sorted in ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
Hre is similar code that you can modify
MyString = "" for Rowcount = 1 to 100 Data = Range("A" & rowCount) if Data = "XYZ" then if MyString = "" then MyString = Data else MyString = MyString & "," & Data end if next RowCount "owlnevada" wrote: I have a subroutine that results in different string lengths sorted in ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
I extracted a part of your suggested code and it ended up with a blank
string. My understanding of the trim function is that it operates on the spaces, while the commas are my concern. Note the one line with two internal commas adjacent each other and the ending comma. Those are the ones I want to clean out along with any at the front of the string. the format shoud look like this: ######, ######, ###### "Dave Peterson" wrote: Maybe something like this as a formula in a worksheet: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ",") MsgBox myStr End Sub VBA's Trim() doesn't do anything with those multiple internal spaces. But application.trim() (the worksheet function), does. owlnevada wrote: I have a subroutine that results in different string lengths sorted in ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
Try this function....
Function FixCommas(ByVal Text As String) As String FixCommas = Trim(Replace(Text, ",", " ")) Do While InStr(FixCommas, " ") FixCommas = Replace(FixCommas, " ", " ") Loop FixCommas = Replace(FixCommas, " ", ", ") End Function You would use it like this... Sub Test() MsgBox FixCommas(" V01234,, V02345, ,12346, 12347, ") End Sub By the way, if you put the function in a Module, it becomes a UDF and you can use it directly on your worksheet if you want. Rick "owlnevada" wrote in message ... I extracted a part of your suggested code and it ended up with a blank string. My understanding of the trim function is that it operates on the spaces, while the commas are my concern. Note the one line with two internal commas adjacent each other and the ending comma. Those are the ones I want to clean out along with any at the front of the string. the format shoud look like this: ######, ######, ###### "Dave Peterson" wrote: Maybe something like this as a formula in a worksheet: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ",") MsgBox myStr End Sub VBA's Trim() doesn't do anything with those multiple internal spaces. But application.trim() (the worksheet function), does. owlnevada wrote: I have a subroutine that results in different string lengths sorted in ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
What string did you start with when you got a blank string returned?
And if you look at the code, it changes commas to spaces, then fixes the spaces, and then changes the spaces back to commas. If you wanted a comma followed by a space: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",", ") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") MsgBox myStr End Sub owlnevada wrote: I extracted a part of your suggested code and it ended up with a blank string. My understanding of the trim function is that it operates on the spaces, while the commas are my concern. Note the one line with two internal commas adjacent each other and the ending comma. Those are the ones I want to clean out along with any at the front of the string. the format shoud look like this: ######, ######, ###### "Dave Peterson" wrote: Maybe something like this as a formula in a worksheet: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ",") MsgBox myStr End Sub VBA's Trim() doesn't do anything with those multiple internal spaces. But application.trim() (the worksheet function), does. owlnevada wrote: I have a subroutine that results in different string lengths sorted in ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
Ok, the string I need this to operate on comes from a series of subs that
pull names from the list of tabnames in a workbook that can have from two to 100 or more worksheets that gets sorted, eliminates duplicates, misc. sheets (which gives the internal commas) etc. The point is to list the name of all the sheets in the Comments box in the BuiltinDocumentProperties field so they will display in the Explorer file dialog window when those properties are toggled on. I've added your code to the end of a sub that gets the list I want but has the commas I want to delete and it comes up blank. . . I'm a little fuzzy on how to get it to operate on this string I call "strComment" but here is my modification or your instance: StrComment = myStr myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") myStr = StrComment ' store the string in the FileComments Property box ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value = StrComment "Dave Peterson" wrote: What string did you start with when you got a blank string returned? And if you look at the code, it changes commas to spaces, then fixes the spaces, and then changes the spaces back to commas. If you wanted a comma followed by a space: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",", ") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") MsgBox myStr End Sub owlnevada wrote: I extracted a part of your suggested code and it ended up with a blank string. My understanding of the trim function is that it operates on the spaces, while the commas are my concern. Note the one line with two internal commas adjacent each other and the ending comma. Those are the ones I want to clean out along with any at the front of the string. the format shoud look like this: ######, ######, ###### "Dave Peterson" wrote: Maybe something like this as a formula in a worksheet: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ",") MsgBox myStr End Sub VBA's Trim() doesn't do anything with those multiple internal spaces. But application.trim() (the worksheet function), does. owlnevada wrote: I have a subroutine that results in different string lengths sorted in ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
StrComment = myStr
myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") myStr = StrComment Look at your code above... you assign myStr to StrComment, change myStr and then assign StrComment (which you **never** touched in the previous 3 lines) back to myStr. So, in essence, this is your "active" code... StrComment = myStr myStr = StrComment If myStr was blank at the start, it over wrote your text (which I assume was stored in StrComment) as a first step. Did you perhaps mean this as your first step? myStr = StrComment If so, I think the rest of the code would be this... myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") StrComment = myStr Rick "owlnevada" wrote in message ... Ok, the string I need this to operate on comes from a series of subs that pull names from the list of tabnames in a workbook that can have from two to 100 or more worksheets that gets sorted, eliminates duplicates, misc. sheets (which gives the internal commas) etc. The point is to list the name of all the sheets in the Comments box in the BuiltinDocumentProperties field so they will display in the Explorer file dialog window when those properties are toggled on. I've added your code to the end of a sub that gets the list I want but has the commas I want to delete and it comes up blank. . . I'm a little fuzzy on how to get it to operate on this string I call "strComment" but here is my modification or your instance: StrComment = myStr myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") myStr = StrComment ' store the string in the FileComments Property box ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value = StrComment "Dave Peterson" wrote: What string did you start with when you got a blank string returned? And if you look at the code, it changes commas to spaces, then fixes the spaces, and then changes the spaces back to commas. If you wanted a comma followed by a space: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",", ") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") MsgBox myStr End Sub owlnevada wrote: I extracted a part of your suggested code and it ended up with a blank string. My understanding of the trim function is that it operates on the spaces, while the commas are my concern. Note the one line with two internal commas adjacent each other and the ending comma. Those are the ones I want to clean out along with any at the front of the string. the format shoud look like this: ######, ######, ###### "Dave Peterson" wrote: Maybe something like this as a formula in a worksheet: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ",") MsgBox myStr End Sub VBA's Trim() doesn't do anything with those multiple internal spaces. But application.trim() (the worksheet function), does. owlnevada wrote: I have a subroutine that results in different string lengths sorted in ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a sorted character string
That swap statement fixed the problemo . . . muchas gracias!
"Rick Rothstein (MVP - VB)" wrote: StrComment = myStr myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") myStr = StrComment Look at your code above... you assign myStr to StrComment, change myStr and then assign StrComment (which you **never** touched in the previous 3 lines) back to myStr. So, in essence, this is your "active" code... StrComment = myStr myStr = StrComment If myStr was blank at the start, it over wrote your text (which I assume was stored in StrComment) as a first step. Did you perhaps mean this as your first step? myStr = StrComment If so, I think the rest of the code would be this... myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") StrComment = myStr Rick "owlnevada" wrote in message ... Ok, the string I need this to operate on comes from a series of subs that pull names from the list of tabnames in a workbook that can have from two to 100 or more worksheets that gets sorted, eliminates duplicates, misc. sheets (which gives the internal commas) etc. The point is to list the name of all the sheets in the Comments box in the BuiltinDocumentProperties field so they will display in the Explorer file dialog window when those properties are toggled on. I've added your code to the end of a sub that gets the list I want but has the commas I want to delete and it comes up blank. . . I'm a little fuzzy on how to get it to operate on this string I call "strComment" but here is my modification or your instance: StrComment = myStr myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") myStr = StrComment ' store the string in the FileComments Property box ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value = StrComment "Dave Peterson" wrote: What string did you start with when you got a blank string returned? And if you look at the code, it changes commas to spaces, then fixes the spaces, and then changes the spaces back to commas. If you wanted a comma followed by a space: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",", ") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ", ") MsgBox myStr End Sub owlnevada wrote: I extracted a part of your suggested code and it ended up with a blank string. My understanding of the trim function is that it operates on the spaces, while the commas are my concern. Note the one line with two internal commas adjacent each other and the ending comma. Those are the ones I want to clean out along with any at the front of the string. the format shoud look like this: ######, ######, ###### "Dave Peterson" wrote: Maybe something like this as a formula in a worksheet: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",") In code: Option Explicit Sub testme() Dim myStr As String myStr = ",,,V01234, V02345, 12346, ,12347," myStr = Replace(myStr, ",", " ") myStr = Application.Trim(myStr) myStr = Replace(myStr, " ", ",") MsgBox myStr End Sub VBA's Trim() doesn't do anything with those multiple internal spaces. But application.trim() (the worksheet function), does. owlnevada wrote: I have a subroutine that results in different string lengths sorted in ascending order that come from the tabnames in a workbook like the following: 12345, ,12345, 12346, , 12345,,12346, 12347, V01234, V02345, 12346, 12347, When a leading comma appears, it's from a tabname that exists but doesn't meet the criteria for the string and the string ends with a comma. How do I ReDim or reformat the string to eliminate the leading, trailing and intermediate double commas? Also, if only one tabname appears in the string, I want to delete that one entirely. The items with V can sort to the end of the string or if with a leading zero and the V is absent, then they sort to the beginning. . . The list should look like a standard CSV list in a string like line 3 above but no ending or intermediate comma. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting No of character in a string | Excel Worksheet Functions | |||
Stepping through each character in a character string | Excel Programming | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
Function to return Character Position of Xth character within a string | Excel Programming | |||
Sorted Fixed Length String | Excel Programming |