Separate where there is "#"

[James O]

Hey all,

Quick question I hope.

I have a word doc with two columns. We click a button to copy data out of a
program and then we paste it into the word doc. At a certain point in the
data there is a # which separates numerical data from the notes.

What I want to do is have the data separate at the #. The portion before
the # going to column 1 and the portion after going to column 2. Thanks very
much for any insight you can offer.

James O

 

[Greg Maxey]

You can split the text using something like:
Sub Test()
Dim oText As DataObject
Set oText = New DataObject
oText.GetFromClipboard
Dim oStr As String
oStr = oText.GetText
MsgBox Left(oStr, InStr(oStr, "#") - 1)
MsgBox Right(oStr, Len(oStr) - InStr(oStr, "#"))
End Sub

 

[Klaus Linke]

Hi James,

It sounds like you want to create a table in Word. In that case, you could
just paste it in, then use "Table > Convert > Text to table", with # as the
field separator ("Other: #").

The macro recorder should give you some code to start with... you then could
edit that, say remove the reference to some fixed number of rows.

Regards,
Klaus