HELP!!How to separate comma and space??

[jepe666]

I have problem,i have data like:
SiteID
=======
0100000, 0201T00, 0301T00, 0401T00

I want get output like:
0100000
0201T00
0301T00 0401T00
I want to disappear comma and space..how can i solve this problem??Help

 

[MisterMo]

replace("0100000, 0201T00, 0301T00, 0401T00", ",", vbCRLF)

-Mo

 

[MisterMo]

or
replace(replace("0100000, 0201T00, 0301T00, 0401T00"," ",""),",", vbCRLF)

-Mo

 

[jepe666]

Thanks to MisterMo
your suggest is correct,but not yet completed..
Bcoz, the data must have array
Please help me..

 

[MisterMo]

Sorry but what exactly do you mean with array? How do you want the data to be displyed?

could you be more specific?

-Mo

If you don't stand for something, you'll fall for anything

 

[jepe666]

I try code like this :
Function remove(ByVal paramsite as String) as String
Dim s As String
Dim words() As String
Dim tempStr As String
s =paramsite
words() = Split(s)
For i = 0 To UBound(words)
tempStr = Replace(words(i), ".", "")
tempStr = Replace(tempStr, ",", "")
tempStr = Replace(tempStr, ":", "")
If Len(tempStr) > 0 Then
Return words.SubString(0,words.Length-2)
End If
Next
End Function

But I got error:
Number of indices is less than the number of dimensions of the indexed array.

Thanks for your advance

 

[MisterMo]

sorry but your logic is quite confusing
1) your function should return a string but you want to return an array

2) you have a return inside the loop without exit function and that will break the code

3) I can only assume that you want to read the array, remove commas(,), ) and (.). And then remove the last two zeros

so IF I read your code right, this might guide you in the right direction, if a function is what you want to use.

    Function remove(ByVal paramsite As String) As String
        Dim RetStr As String
        Dim s As String
        Dim i As Integer
        Dim words() As String
        Dim tempStr As String
        s = paramsite
        words = Split(s, ",")
        For i = 0 To UBound(words)
            tempStr = Replace(words(i), ".", "")
            tempStr = Replace(tempStr, ",", "")
            tempStr = Replace(tempStr, ":", "")
            If Len(tempStr) > 0 Then
                words(i) = Left(words(i), words(i).Length - 2)
            End If
        Next
        RetStr = Join(words, vbCrLf)
        Return RetStr
    End Function

obviously there are other ways to do this but I am still not sure of what you really want for your report

good luck

-Mo

If you don't stand for something, you'll fall for anything

 

[jepe666]

Thanks alot Mister Mo..