Separate where there is "#"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James O - 19 Mar 2007 20:50 GMT

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

Reply to this Message

Greg Maxey - 19 Mar 2007 21:10 GMT

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

> Hey all,
>
[quoted text clipped - 9 lines]
>
> James O

Reply to this Message

Klaus Linke - 26 Mar 2007 02:16 GMT

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

> Hey all,
>
[quoted text clipped - 11 lines]
>
> James O

Reply to this Message