Clean a string

[nabbster]

Hi guys
Got a bit of brain bashing for you, i am well aware of how to clean a string of VbCrLf and also how to split data but my brain is stuck as to the best way to do what i want to do.

basically i have a string of clipboard data that is tab delimited and pasts nicely into Excel. problem is there are occasionally some unexpected VbCrLf. at first i thought ah thats easy so i did the following

Public MyDataObj As New DataObject
Public StrInput() As String
Public StrFinal As String

Private Sub CommandButton1_Click()
MyDataObj.GetFromClipboard
StrInput = Replace(MyDataObj.GetText, vbCrLf, "")
MyDataObj.SetText ("")
MyDataObj.PutInClipboard
End Sub

almost works but i overlooked the carriage return that is needed at the end of each line to put the data in rows. just so its clear its like this

Data1 vbtab Data2 vbtab Data3 vbcrlf
Data1 vbtab data2 vbtab Data3 vbcrlf

So you see my above code will remove the legitimate VbCrLf any ideas?

[salvelinus]

How about using Replace()? Possibly you'll have to use the ASCII characters, Asc(10) & Asc(13), IIRC.

[westconn1]

this is very difficult, because if there is a crlf in data3 or data1, how to tell which is the legitimate one to split data3 and next data1
easy enough to check out data2
can you sort it in the data source, before it is copied to the clipboard

[nabbster]

Idd Westconn1 i know my original code i posted was wrong i have changed it now i posted that because i had concidered splitting the string by vbtabs then only cleaning certain strings. i am well aware of the replace function, anyway my thought was there are 87 fields tab delimited so my thought allthough i cant quite put my brain into code was split at tabs, then
loop through the string only cleaning every 87th string.

so a little math is required i think.

[Doogle]

I think it'll be a bit more complicated than that, if you split on Tab, the end of one row will 'run in' to the beginning of the next.
eg
123<TAB>456<vbCrLf>
789<TAB>ABC<vbCrLf>

When split on <TAB> the result will be

123
456<vbCrLf>789
ABC etc

I've had a play and I think the solution might be to Replace vbCrLf with vbTab first, then replace any pairs of vbTabs with one vbTab (two together would indicate that there was a rogue vbCrLf), split on vbTab, and then 'glue it back together' adding vbTabs and vbCrLf where required.

Something like this perhaps:

Private Sub Command1_Click()
Dim strChars As String
Dim strSplit() As String
Dim strResult As String
Dim lngI As Long
'
' Set up some data
' Each row should have 3 cols and be terminated by a vbCrLf
' plant an extra vbCrLf somewhere
'
strChars = "abcd" & vbTab & "efgh" & vbTab & vbCrLf & "ijkl" & vbCrLf
strChars = strChars & "1234" & vbTab & "5678" & vbTab & vbCrLf & "xyz"

strChars = Replace(strChars, vbCrLf, vbTab)
strChars = Replace(strChars, vbTab & vbTab, vbTab)
strSplit = Split(strChars, vbTab)
For lngI = 1 To UBound(strSplit) + 1
    strResult = strResult & strSplit(lngI - 1) & vbTab
    If lngI Mod 3 = 0 Then
        strResult = Left(strResult, Len(strResult) - 1)
        strResult = strResult & vbCrLf
    End If
Next lngI
Debug.Print strResult
End Sub

In your case

    If intI Mod 3 = 0 Then

would be

    If intI Mod 87 = 0 Then

Edit: This doesn't cope with the situation where you might have ABC<vbCrLf>D<TAB>EFGH<TAB>..... etc. Also if there are two vbCrLfs together in the original you should replace pairs of those before doing anything

strChars = Replace(strChars, vbCrLf & vbCrLf, vbCrLf)

It depends where the rogue vbCrLfs actually are so you'll probably have to play around with the code a bit.

Of course, the best solution would be to 'clean up' the original data.

[nabbster]

Thanks for the reply ill certainly look into that i think i may of found a solution the form is an outlook form that the data starts life in and it looks like i can just disable the enterkey behaviour thus removing any chance of unwanted VbVrLf

ill give it a goe how funtionall it is thanks anyway