I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

When you do File|Open, you should be able to specify Delimited (by a comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

"naulerich" wrote in message
...
I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!


Two thoughts:

1) Any chance the file also happens to have the data in nice, neat columns,
so it could be imported as fixed width?

2) Do you have any control over the program which created the text file? If
yes, perhaps you could design an export report which would delimit the
fields using a character other than commas, like the tilde ~ symbol.

In Excel 2000, it doesn't "respect" the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text"
and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text"
and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson

Now, that's interesting. The test strings I used were numerical, and looked
like the line below:
"1234,5678,9" Excel broke that into three columns. I wonder if it behaves
differently when the strings in question are numerical.

Time for the OP to return and tell us worked or didn't work for him.

"Dave Peterson" wrote in message
...
I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text strings
with
commas within them. Excel interprets them as delimiters, regardless of
the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text"
and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson

I am using 2003 it is not respecting the ""...I am going to have the souce
data delimiter changes to a ~ and see if that helps.

Thanks!



"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text"
and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson

I think you should copy a few of the lines from the text file into your followup
post--if you don't get it working.



naulerich wrote:

I am using 2003 it is not respecting the ""...I am going to have the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text"
and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats we
must prep for migrating sites to the latest 4E format. This requirement is in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and using
the latest claims submission formats we must prep for migrating sites to the
latest 4E format. This requirement is in sync with the EDI requirement with
the same name. This is a multi-step project.~ <<internal source group~ Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.~ Need to activate Patient and Account Alert during patient check-in.
Not currently working in Patient Check-in.~ <<internal source group~ Nice
to Have~ ~

"Dave Peterson" wrote:

I think you should copy a few of the lines from the text file into your followup
post--if you don't get it working.



naulerich wrote:

I am using 2003 it is not respecting the ""...I am going to have the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text"
and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

It appears you've changed commas to tilde signs. This that make the import
process work correctly?


"naulerich" wrote in message
...
Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats
we
must prep for migrating sites to the latest 4E format. This requirement is
in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and
using
the latest claims submission formats we must prep for migrating sites to
the
latest 4E format. This requirement is in sync with the EDI requirement
with
the same name. This is a multi-step project.~ <<internal source group~
Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them
to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.~ Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.~ <<internal source group~
Nice
to Have~ ~

"Dave Peterson" wrote:

I think you should copy a few of the lines from the text file into your
followup
post--if you don't get it working.



naulerich wrote:

I am using 2003 it is not respecting the ""...I am going to have the
souce
data delimiter changes to a ~ and see if that helps.

Thanks!

"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember it
working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one
field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text
strings with
commas within them. Excel interprets them as delimiters, regardless
of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited
(by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test
text"
and
"this is a test" columns therefore text to columns wont work.
However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

I don't see the double quotes. I don't see the commas between fields.

But if each of those paragraphs is actually a line in a text file, I could
import the data using delimited by tilde (~) and got different fields.

naulerich wrote:

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats we
must prep for migrating sites to the latest 4E format. This requirement is in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and using
the latest claims submission formats we must prep for migrating sites to the
latest 4E format. This requirement is in sync with the EDI requirement with
the same name. This is a multi-step project.~ <<internal source group~ Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.~ Need to activate Patient and Account Alert during patient check-in.
Not currently working in Patient Check-in.~ <<internal source group~ Nice
to Have~ ~

"Dave Peterson" wrote:

I think you should copy a few of the lines from the text file into your followup
post--if you don't get it working.



naulerich wrote:

I am using 2003 it is not respecting the ""...I am going to have the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text"
and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the import
with delimiter set to ~....I don't know what to do next...

"Dave Peterson" wrote:

I don't see the double quotes. I don't see the commas between fields.

But if each of those paragraphs is actually a line in a text file, I could
import the data using delimited by tilde (~) and got different fields.

naulerich wrote:

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats we
must prep for migrating sites to the latest 4E format. This requirement is in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and using
the latest claims submission formats we must prep for migrating sites to the
latest 4E format. This requirement is in sync with the EDI requirement with
the same name. This is a multi-step project.~ <<internal source group~ Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.~ Need to activate Patient and Account Alert during patient check-in.
Not currently working in Patient Check-in.~ <<internal source group~ Nice
to Have~ ~

"Dave Peterson" wrote:

I think you should copy a few of the lines from the text file into your followup
post--if you don't get it working.



naulerich wrote:

I am using 2003 it is not respecting the ""...I am going to have the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text"
and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Please describe the exact things you're doing after you open the file and
the import wizard appears.



"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the import
with delimiter set to ~....I don't know what to do next...

"Dave Peterson" wrote:

I don't see the double quotes. I don't see the commas between fields.

But if each of those paragraphs is actually a line in a text file, I
could
import the data using delimited by tilde (~) and got different fields.

naulerich wrote:

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared
for
MSP, electronic secondary, and using the latest claims submission
formats we
must prep for migrating sites to the latest 4E format. This requirement
is in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format.
To
ensure that all sites are prepared for MSP, electronic secondary, and
using
the latest claims submission formats we must prep for migrating sites
to the
latest 4E format. This requirement is in sync with the EDI requirement
with
the same name. This is a multi-step project.~ <<internal source
group~ Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up
the
detail on the patient, alerts are not presented to the user causing
them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in
list,
the user will make or take the proper action necessary based on the
alert
message.~ Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.~ <<internal source group~
Nice
to Have~ ~

"Dave Peterson" wrote:

I think you should copy a few of the lines from the text file into
your followup
post--if you don't get it working.



naulerich wrote:

I am using 2003 it is not respecting the ""...I am going to have
the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember it
working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one
field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text
strings with
commas within them. Excel interprets them as delimiters,
regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify
Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the
"test text"
and
"this is a test" columns therefore text to columns wont
work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Choose Delimitted----Next---Choose Other and insert a ~----Next----Finish

Anyway I could send you a sample of the output to review?

"Doug Kanter" wrote:

Please describe the exact things you're doing after you open the file and
the import wizard appears.



"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the import
with delimiter set to ~....I don't know what to do next...

"Dave Peterson" wrote:

I don't see the double quotes. I don't see the commas between fields.

But if each of those paragraphs is actually a line in a text file, I
could
import the data using delimited by tilde (~) and got different fields.

naulerich wrote:

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared
for
MSP, electronic secondary, and using the latest claims submission
formats we
must prep for migrating sites to the latest 4E format. This requirement
is in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format.
To
ensure that all sites are prepared for MSP, electronic secondary, and
using
the latest claims submission formats we must prep for migrating sites
to the
latest 4E format. This requirement is in sync with the EDI requirement
with
the same name. This is a multi-step project.~ <<internal source
group~ Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up
the
detail on the patient, alerts are not presented to the user causing
them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in
list,
the user will make or take the proper action necessary based on the
alert
message.~ Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.~ <<internal source group~
Nice
to Have~ ~

"Dave Peterson" wrote:

I think you should copy a few of the lines from the text file into
your followup
post--if you don't get it working.



naulerich wrote:

I am using 2003 it is not respecting the ""...I am going to have
the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember it
working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one
field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has text
strings with
commas within them. Excel interprets them as delimiters,
regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify
Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the
"test text"
and
"this is a test" columns therefore text to columns wont
work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

On Mon, 3 Apr 2006 08:50:01 -0700, naulerich
wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

It seems to work fine here in XL2002 using the Data/Text to
Columns/Delimited/Comma AND ALSO indicate that the " is the text qualifier


--ron

On Mon, 3 Apr 2006 08:50:01 -0700, naulerich
wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

To expand on my previous, on the page where you set the Delimiter, there is
another box indicating Text Qualifier. If this is NOT set to the double quote
("), then it will misbehave similar to what you describe.


--ron

"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the import
with delimiter set to ~....I don't know what to do next...

You shouldn't have removed the double quotes, and you didn't need to change
the commas to tildes. Go back to your original file.

You should use your comma as the delimiter, but in the "text qualifier" box
of the text import wizard you need the double quote symbol.
--
David Biddulph

Yes. The email address I use here is functional: .
I may not be able to look at it until much later tonight, or tomorrow
morning at the latest.

"naulerich" wrote in message
...
Choose Delimitted----Next---Choose Other and insert a
~----Next----Finish

Anyway I could send you a sample of the output to review?

"Doug Kanter" wrote:

Please describe the exact things you're doing after you open the file and
the import wizard appears.



"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the
import
with delimiter set to ~....I don't know what to do next...

"Dave Peterson" wrote:

I don't see the double quotes. I don't see the commas between fields.

But if each of those paragraphs is actually a line in a text file, I
could
import the data using delimited by tilde (~) and got different fields.

naulerich wrote:

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites
are
utilizing the WEBMD_NSF Format. To ensure that all sites are
prepared
for
MSP, electronic secondary, and using the latest claims submission
formats we
must prep for migrating sites to the latest 4E format. This
requirement
is in
sync with the EDI requirement with the same name. This is a
multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF
Format.
To
ensure that all sites are prepared for MSP, electronic secondary,
and
using
the latest claims submission formats we must prep for migrating
sites
to the
latest 4E format. This requirement is in sync with the EDI
requirement
with
the same name. This is a multi-step project.~ <<internal source
group~ Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings
up
the
detail on the patient, alerts are not presented to the user causing
them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in
list,
the user will make or take the proper action necessary based on the
alert
message.~ Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.~ <<internal source
group~
Nice
to Have~ ~

"Dave Peterson" wrote:

I think you should copy a few of the lines from the text file into
your followup
post--if you don't get it working.



naulerich wrote:

I am using 2003 it is not respecting the ""...I am going to have
the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

"Dave Peterson" wrote:

I don't have xl2k anymore, but that's not the way I remember
it
working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was
one
field).

Can you try it one more time?

Doug Kanter wrote:

In Excel 2000, it doesn't "respect" the commas. The OP has
text
strings with
commas within them. Excel interprets them as delimiters,
regardless of the
quotes.

"Dave Peterson" wrote in message
...
When you do File|Open, you should be able to specify
Delimited (by a
comma).
Excel will respect those strings within double quotes.



naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out
the
"test text"
and
"this is a test" columns therefore text to columns wont
work. However
what I
need to show in each separate column is enclosed in
quotes.

Any help would be appreciated!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

"David Biddulph" wrote in message
...
"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the import
with delimiter set to ~....I don't know what to do next...

You shouldn't have removed the double quotes, and you didn't need to
change the commas to tildes. Go back to your original file.

You should use your comma as the delimiter, but in the "text qualifier"
box of the text import wizard you need the double quote symbol.
--
David Biddulph


Unless I misread the original post, he said there were commas WITHIN the
text that he wanted to keep intact - not just commas as delimiters. If this
is the case, Excel will split fields based not only on the commas which
exist as delimiters, but also when it sees those within the text.

you have a lot of Text in your text file. There does not seem to be any
numbers for calculations. Why don't you import to Word?.

Alternatively, you will have to use a macro to import the text

http://www.geocities.com/excelmarksway

exceluserforeman

"naulerich" wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

"Doug Kanter" wrote in message
...

"David Biddulph" wrote in message
...
"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the
import
with delimiter set to ~....I don't know what to do next...

You shouldn't have removed the double quotes, and you didn't need to
change the commas to tildes. Go back to your original file.

You should use your comma as the delimiter, but in the "text qualifier"
box of the text import wizard you need the double quote symbol.

Unless I misread the original post, he said there were commas WITHIN the
text that he wanted to keep intact - not just commas as delimiters. If
this is the case, Excel will split fields based not only on the commas
which exist as delimiters, but also when it sees those within the text.

No. That's what the text qualifier sorts out for you. Try it.
--
David Biddulph

"Doug Kanter" wrote in message
...
"Dave Peterson" wrote in message
...
I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?


Now, that's interesting. The test strings I used were numerical, and
looked like the line below:
"1234,5678,9" Excel broke that into three columns. I wonder if it behaves
differently when the strings in question are numerical.

Interesting, as you say. I found that it kept the 1234,5678,9 together if
the preceding double quote mark is immediately after the comma, but not if
there is a space between.

The first line here keeps the numeric string together, but the second line
splits it:
"123","test, text","this is a test","abc","1234,5678,9"
"123","test, text","this is a test","abc", "1234,5678,9"
--
David Biddulph

If I included the space character (as well as the comma), then both lines

"123","test, text","this is a test","abc","1234,5678,9"
"123","test, text","this is a test","abc", "1234,5678,9"

were imported the same--the last field was 1234,5678,9 (all one cell).

David Biddulph wrote:

"Doug Kanter" wrote in message
...
"Dave Peterson" wrote in message
...
I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

Now, that's interesting. The test strings I used were numerical, and
looked like the line below:
"1234,5678,9" Excel broke that into three columns. I wonder if it behaves
differently when the strings in question are numerical.

Interesting, as you say. I found that it kept the 1234,5678,9 together if
the preceding double quote mark is immediately after the comma, but not if
there is a space between.

The first line here keeps the numeric string together, but the second line
splits it:
"123","test, text","this is a test","abc","1234,5678,9"
"123","test, text","this is a test","abc", "1234,5678,9"
--
David Biddulph

--

Dave Peterson

"David Biddulph" wrote in message
...
"Doug Kanter" wrote in message
...

"David Biddulph" wrote in message
...
"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the
import
with delimiter set to ~....I don't know what to do next...

You shouldn't have removed the double quotes, and you didn't need to
change the commas to tildes. Go back to your original file.

You should use your comma as the delimiter, but in the "text qualifier"
box of the text import wizard you need the double quote symbol.

Unless I misread the original post, he said there were commas WITHIN the
text that he wanted to keep intact - not just commas as delimiters. If
this is the case, Excel will split fields based not only on the commas
which exist as delimiters, but also when it sees those within the text.

No. That's what the text qualifier sorts out for you. Try it.
--
David Biddulph


In theory, yes, but the OP e-mailed me some of her original text (with
commas, not tildes), and doing what you suggest, the import does NOT work
correctly. Excel sees the commas WITHIN the text strings as delimiters, same
as the ones BETWEEN the text strings. Here's the text she sent:

"5493", "BASE-(Claims) Auto Format Update I", "Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats we
must prep for migrating sites to the latest 4E format. This requirement is
in sync with the EDI requirement with the same name. This is a multi-step
project.", "Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and using
the latest claims submission formats we must prep for migrating sites to the
latest 4E format. This requirement is in sync with the EDI requirement with
the same name. This is a multi-step project.", "<<internal source group",
"Have to Have", "", ""

"5501", "BASE_Patient/Account Alert in Check-in", "Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.", "Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.", "<<internal source group",
"Nice to Have", "", ""

"3943", "BASE-Account Select Window Default Method", "Intergy today allows
users to designate their user preference when selecting patients. Users
would like to have this same feature when selecting accounts. By providing
the user to designate their "default" account select method in user
preference, it will allow them to save key strokes in the account select
windows.", "Provide the same method in Patient Select Window -- ability for
the user to default their search.
In the User Preference for Patient Select we called the Preference --
"Select Patient Sort/Filter Default". Need to do the same for Account
Select.", "<<internal source group", "Nice to Have", "", ""

Nichole, using the sample text you e-mailed me, I got the same results you
did. This leads to another question - same one I asked yesterday:

Do you have any control over the program which PRODUCES the text file? What
is that program?

I think that the OP will have to clean up the data.

There shouldn't be spaces between fields and any field that contains double
quotes will have to have those double quotes doubled up.

In that last line,
their "default" account
would have to become
their ""default"" account

I don't see a way around it except to clean up that data.

Doug Kanter wrote:

"David Biddulph" wrote in message
...
"Doug Kanter" wrote in message
...

"David Biddulph" wrote in message
...
"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the
import
with delimiter set to ~....I don't know what to do next...

You shouldn't have removed the double quotes, and you didn't need to
change the commas to tildes. Go back to your original file.

You should use your comma as the delimiter, but in the "text qualifier"
box of the text import wizard you need the double quote symbol.

Unless I misread the original post, he said there were commas WITHIN the
text that he wanted to keep intact - not just commas as delimiters. If
this is the case, Excel will split fields based not only on the commas
which exist as delimiters, but also when it sees those within the text.

No. That's what the text qualifier sorts out for you. Try it.
--
David Biddulph


In theory, yes, but the OP e-mailed me some of her original text (with
commas, not tildes), and doing what you suggest, the import does NOT work
correctly. Excel sees the commas WITHIN the text strings as delimiters, same
as the ones BETWEEN the text strings. Here's the text she sent:

"5493", "BASE-(Claims) Auto Format Update I", "Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats we
must prep for migrating sites to the latest 4E format. This requirement is
in sync with the EDI requirement with the same name. This is a multi-step
project.", "Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and using
the latest claims submission formats we must prep for migrating sites to the
latest 4E format. This requirement is in sync with the EDI requirement with
the same name. This is a multi-step project.", "<<internal source group",
"Have to Have", "", ""

"5501", "BASE_Patient/Account Alert in Check-in", "Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.", "Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.", "<<internal source group",
"Nice to Have", "", ""

"3943", "BASE-Account Select Window Default Method", "Intergy today allows
users to designate their user preference when selecting patients. Users
would like to have this same feature when selecting accounts. By providing
the user to designate their "default" account select method in user
preference, it will allow them to save key strokes in the account select
windows.", "Provide the same method in Patient Select Window -- ability for
the user to default their search.
In the User Preference for Patient Select we called the Preference --
"Select Patient Sort/Filter Default". Need to do the same for Account
Select.", "<<internal source group", "Nice to Have", "", ""

--

Dave Peterson

"Doug Kanter" wrote in message
...

"David Biddulph" wrote in message
...
"Doug Kanter" wrote in message
...

"David Biddulph" wrote in message
...
"naulerich" wrote in message
...
I did remove the double quotes and added a ~ instead of the commas, I
am
still not getting the output in the correct columns when using the
import
with delimiter set to ~....I don't know what to do next...

You shouldn't have removed the double quotes, and you didn't need to
change the commas to tildes. Go back to your original file.

You should use your comma as the delimiter, but in the "text qualifier"
box of the text import wizard you need the double quote symbol.

Unless I misread the original post, he said there were commas WITHIN the
text that he wanted to keep intact - not just commas as delimiters. If
this is the case, Excel will split fields based not only on the commas
which exist as delimiters, but also when it sees those within the text.

No. That's what the text qualifier sorts out for you. Try it.
--
David Biddulph

In theory, yes, but the OP e-mailed me some of her original text (with
commas, not tildes), and doing what you suggest, the import does NOT work
correctly. Excel sees the commas WITHIN the text strings as delimiters,
same as the ones BETWEEN the text strings. Here's the text she sent:

"5493", "BASE-(Claims) Auto Format Update I", "Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats
we must prep for migrating sites to the latest 4E format. This requirement
is in sync with the EDI requirement with the same name. This is a
multi-step project.", "Today most of our sites are utilizing the WEBMD_NSF
Format. To ensure that all sites are prepared for MSP, electronic
secondary, and using the latest claims submission formats we must prep for
migrating sites to the latest 4E format. This requirement is in sync with
the EDI requirement with the same name. This is a multi-step project.",
"<<internal source group", "Have to Have", "", ""

"5501", "BASE_Patient/Account Alert in Check-in", "Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them
to miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.", "Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.", "<<internal source group",
"Nice to Have", "", ""

"3943", "BASE-Account Select Window Default Method", "Intergy today allows
users to designate their user preference when selecting patients. Users
would like to have this same feature when selecting accounts. By providing
the user to designate their "default" account select method in user
preference, it will allow them to save key strokes in the account select
windows.", "Provide the same method in Patient Select Window -- ability
for the user to default their search.
In the User Preference for Patient Select we called the Preference --
"Select Patient Sort/Filter Default". Need to do the same for Account
Select.", "<<internal source group", "Nice to Have", "", ""

If you change each occurrence of , " [comma, space, double quote] to ,"
[comma, double quote], then it will allow the import wizard to pick up the
double quote text qualifier art the start of each field, and hence it won't
split the field at a comma within the double quotes.

The only remaining problem is the additional double quotes around "default"
in the final data set. As Dave Peterson has said, in that case you'll need
to double up the double quotes. It then works OK.
--

The text is coming out of a Borland database (Caliber), it will export to
Word no problem however it is requested the output be in .xls format which is
not supported through the tools we have purchased through Borland. I am not
sure what control the BA has, other than setting delimiters when
exporting...What can I ask that the BA do to get a clean export?

"Doug Kanter" wrote:

Nichole, using the sample text you e-mailed me, I got the same results you
did. This leads to another question - same one I asked yesterday:

Do you have any control over the program which PRODUCES the text file? What
is that program?

Most database products allow the user to design outbound reports in any way
that's necessary. Although programmers are as likely to do stupid things as
the rest of the population, I'd be very surprised if Borland built that
product without the same report design features as their older products
(Paradox, Delphi).

This link takes you to the Borland support forums - I'd ask there about
whether you can design an export report that's more manageable than the file
you're struggling with now:
http://support.borland.com/category.jspa?categoryID=3

This might take the discussion into another direction, but what's the final
purpose of bringing all that text into Excel?

"naulerich" wrote in message
...
The text is coming out of a Borland database (Caliber), it will export to
Word no problem however it is requested the output be in .xls format which
is
not supported through the tools we have purchased through Borland. I am
not
sure what control the BA has, other than setting delimiters when
exporting...What can I ask that the BA do to get a clean export?

"Doug Kanter" wrote:

Nichole, using the sample text you e-mailed me, I got the same results
you
did. This leads to another question - same one I asked yesterday:

Do you have any control over the program which PRODUCES the text file?
What
is that program?

I know that the capability to export to Excel is there however, I have been
told that is outside of the reporting functionality we purchased. Bottom
line, we are trying to find a work around, currently the data is being
maniputlated close to 20 hrs per output (not sure what takes so long...) to
get into the pretty excel colums. I believe that the final reason for the
output in excel is for R&D tracking of projects/deliverables. My comapny
LOVES excel, spreadsheet heaven here...

Can't very well ask Borland what to do...


"Doug Kanter" wrote:

Most database products allow the user to design outbound reports in any way
that's necessary. Although programmers are as likely to do stupid things as
the rest of the population, I'd be very surprised if Borland built that
product without the same report design features as their older products
(Paradox, Delphi).

This link takes you to the Borland support forums - I'd ask there about
whether you can design an export report that's more manageable than the file
you're struggling with now:
http://support.borland.com/category.jspa?categoryID=3

This might take the discussion into another direction, but what's the final
purpose of bringing all that text into Excel?

"naulerich" wrote in message
...
The text is coming out of a Borland database (Caliber), it will export to
Word no problem however it is requested the output be in .xls format which
is
not supported through the tools we have purchased through Borland. I am
not
sure what control the BA has, other than setting delimiters when
exporting...What can I ask that the BA do to get a clean export?

"Doug Kanter" wrote:

Nichole, using the sample text you e-mailed me, I got the same results
you
did. This leads to another question - same one I asked yesterday:

Do you have any control over the program which PRODUCES the text file?
What
is that program?

Is there a name for the reporting capability that you DO have? A product
description? I know some ancient Borland product veterans who might have
some tricks up their sleeves, but I'd need to tell them what you've got.


"naulerich" wrote in message
...
I know that the capability to export to Excel is there however, I have
been
told that is outside of the reporting functionality we purchased. Bottom
line, we are trying to find a work around, currently the data is being
maniputlated close to 20 hrs per output (not sure what takes so long...)
to
get into the pretty excel colums. I believe that the final reason for the
output in excel is for R&D tracking of projects/deliverables. My comapny
LOVES excel, spreadsheet heaven here...

Can't very well ask Borland what to do...


"Doug Kanter" wrote:

Most database products allow the user to design outbound reports in any
way
that's necessary. Although programmers are as likely to do stupid things
as
the rest of the population, I'd be very surprised if Borland built that
product without the same report design features as their older products
(Paradox, Delphi).

This link takes you to the Borland support forums - I'd ask there about
whether you can design an export report that's more manageable than the
file
you're struggling with now:
http://support.borland.com/category.jspa?categoryID=3

This might take the discussion into another direction, but what's the
final
purpose of bringing all that text into Excel?

"naulerich" wrote in message
...
The text is coming out of a Borland database (Caliber), it will export
to
Word no problem however it is requested the output be in .xls format
which
is
not supported through the tools we have purchased through Borland. I
am
not
sure what control the BA has, other than setting delimiters when
exporting...What can I ask that the BA do to get a clean export?

"Doug Kanter" wrote:

Nichole, using the sample text you e-mailed me, I got the same results
you
did. This leads to another question - same one I asked yesterday:

Do you have any control over the program which PRODUCES the text file?
What
is that program?

Emailed you the response from the BA.

"Doug Kanter" wrote:

Is there a name for the reporting capability that you DO have? A product
description? I know some ancient Borland product veterans who might have
some tricks up their sleeves, but I'd need to tell them what you've got.


"naulerich" wrote in message
...
I know that the capability to export to Excel is there however, I have
been
told that is outside of the reporting functionality we purchased. Bottom
line, we are trying to find a work around, currently the data is being
maniputlated close to 20 hrs per output (not sure what takes so long...)
to
get into the pretty excel colums. I believe that the final reason for the
output in excel is for R&D tracking of projects/deliverables. My comapny
LOVES excel, spreadsheet heaven here...

Can't very well ask Borland what to do...


"Doug Kanter" wrote:

Most database products allow the user to design outbound reports in any
way
that's necessary. Although programmers are as likely to do stupid things
as
the rest of the population, I'd be very surprised if Borland built that
product without the same report design features as their older products
(Paradox, Delphi).

This link takes you to the Borland support forums - I'd ask there about
whether you can design an export report that's more manageable than the
file
you're struggling with now:
http://support.borland.com/category.jspa?categoryID=3

This might take the discussion into another direction, but what's the
final
purpose of bringing all that text into Excel?

"naulerich" wrote in message
...
The text is coming out of a Borland database (Caliber), it will export
to
Word no problem however it is requested the output be in .xls format
which
is
not supported through the tools we have purchased through Borland. I
am
not
sure what control the BA has, other than setting delimiters when
exporting...What can I ask that the BA do to get a clean export?

"Doug Kanter" wrote:

Nichole, using the sample text you e-mailed me, I got the same results
you
did. This leads to another question - same one I asked yesterday:

Do you have any control over the program which PRODUCES the text file?
What
is that program?

On Mon, 3 Apr 2006 08:50:01 -0700, naulerich
wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

Having read a bunch of this thread, it seems to me that you may be able to
parse out your data using Regular Expressions in VBA.

To enter the code, <alt<F11 opens the VBA Editor. Ensure your project is
highlighted in the project explorer window, then Insert Module and paste the
code below into the window that opens. In the VB Editor, you must also select
Tools/References and select Microsoft VBScript Regular Expressions 5.5 from the
list.

To use this, with your data in A1, enter the following formula into B1 and
copy/drag across as far as required.

=remid($A1,"""[^""]+""",COLUMNS($B:B))

The Regular Expression portion: """[^""]+""" says generate a match that
begins with a double quote; is followed by any number of characters that do not
include a double quote; and terminated by a double quote.

The Columns function is merely a method of generating an increasing number as
you copy/drag the formula across several columns; and it will cause the
expression to return the 1st, 2nd, etc instance of the matched pattern.

The resultant string will have double quotes around it. If that is not
satisfactory, you can embed the above in a SUBSTITUTE function to get rid of
them.

=SUBSTITUTE(remid($A1,"""[^""]+""",COLUMNS($B:B)),"""","")

============================================
Option Explicit
Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter
Dim t() As String 'container for array results

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function
=============================

Let me know if this works for you.



--ron