Format a sorted character string

[owlnevada]

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]

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.

 

[Joel]

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]

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: ######, ######, ######

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Dave Peterson]

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]

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 File>Comments Property box
ActiveWorkbook.BuiltinDocumentProperties("Comments").Value = StrComment

 

[Rick Rothstein \(MVP - VB\)]

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

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 File>Comments Property box
ActiveWorkbook.BuiltinDocumentProperties("Comments").Value = StrComment

 

[owlnevada]

That swap statement fixed the problemo . . . muchas gracias!