Where is the information you are pulling in located at... In a variable? In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to put it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a variable? In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can manipulate
it? Do you put the result of the query in a variable, array, worksheet, the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

Rick,
I can put results it into the textbox then manipulate it from there or how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can manipulate
it? Do you put the result of the query in a variable, array, worksheet, the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment statement to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there or how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array, worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one
per
line) on a worksheet? The Textbox itself? An array variable? Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

Rick
Here is what I have but it is leaving off the last part of the string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
..Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
..AddItem sO
sO = ""
End If
Next
End With
End Sub


"Rick Rothstein" wrote:

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment statement to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there or how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array, worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one
per
line) on a worksheet? The Textbox itself? An array variable? Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

I left that part off on purpose because your first post didn't show it in
the "what I want it to look like afterwards" section... removing the
next-to-last statement will allow that part of the text to show...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
TextBox1.Text = Txt

I see you also switched from a TextBox (again, mentioned in your first post)
to a ListBox. I'm not sure if that was a desired change or only one you made
to try and solve the problem you were having. The above code assumes you
still want to use a TextBox. If not, write back and I'll modify it for a
ListBox.

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick
Here is what I have but it is leaving off the last part of the string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub


"Rick Rothstein" wrote:

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment statement
to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton
Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there or
how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you
query
the database, the result of that query goes somewhere... my question
is
where? We can pull the text apart relatively easily, but I need to
know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array,
worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im
using
ADODB/SQL query to get the data out of the database. I would like
to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one
per
line) on a worksheet? The Textbox itself? An array variable? Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00