I have a .txt file that looks like:
quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
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 quot;test textquot; and
quot;this is a testquot; 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:
gt;
gt; I have a .txt file that looks like:
gt;
gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt;
gt; I want my output in excel to be the following:
gt;
gt; A1=123
gt; B1=test text
gt; C1=this is a test
gt; D1=abc
gt;
gt; Potential problem is that there are commas through out the quot;test textquot; and
gt; quot;this is a testquot; columns therefore text to columns wont work. However what I
gt; need to show in each separate column is enclosed in quotes.
gt;
gt; Any help would be appreciated!
--
Dave Peterson
quot;naulerichquot; gt; wrote in message
...
gt;I have a .txt file that looks like:
gt;
gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt;
gt; I want my output in excel to be the following:
gt;
gt; A1=123
gt; B1=test text
gt; C1=this is a test
gt; D1=abc
gt;
gt; Potential problem is that there are commas through out the quot;test textquot; and
gt; quot;this is a testquot; columns therefore text to columns wont work. However
gt; what I
gt; need to show in each separate column is enclosed in quotes.
gt;
gt; Any help would be appreciated!
gt;
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 quot;respectquot; the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.
quot;Dave Petersonquot; gt; wrote in message
...
gt; When you do File|Open, you should be able to specify Delimited (by a
gt; comma).
gt; Excel will respect those strings within double quotes.
gt;
gt;
gt;
gt; naulerich wrote:
gt;gt;
gt;gt; I have a .txt file that looks like:
gt;gt;
gt;gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt;gt;
gt;gt; I want my output in excel to be the following:
gt;gt;
gt;gt; A1=123
gt;gt; B1=test text
gt;gt; C1=this is a test
gt;gt; D1=abc
gt;gt;
gt;gt; Potential problem is that there are commas through out the quot;test textquot;
gt;gt; and
gt;gt; quot;this is a testquot; columns therefore text to columns wont work. However
gt;gt; what I
gt;gt; need to show in each separate column is enclosed in quotes.
gt;gt;
gt;gt; Any help would be appreciated!
gt;
gt; --
gt;
gt; Dave Peterson
I don't have xl2k anymore, but that's not the way I remember it working.
I put:
quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
in a text file and xl2003 imported it nicely (test, text was one field).
Can you try it one more time?
Doug Kanter wrote:
gt;
gt; In Excel 2000, it doesn't quot;respectquot; the commas. The OP has text strings with
gt; commas within them. Excel interprets them as delimiters, regardless of the
gt; quotes.
gt;
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt; gt; When you do File|Open, you should be able to specify Delimited (by a
gt; gt; comma).
gt; gt; Excel will respect those strings within double quotes.
gt; gt;
gt; gt;
gt; gt;
gt; gt; naulerich wrote:
gt; gt;gt;
gt; gt;gt; I have a .txt file that looks like:
gt; gt;gt;
gt; gt;gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt;gt;
gt; gt;gt; I want my output in excel to be the following:
gt; gt;gt;
gt; gt;gt; A1=123
gt; gt;gt; B1=test text
gt; gt;gt; C1=this is a test
gt; gt;gt; D1=abc
gt; gt;gt;
gt; gt;gt; Potential problem is that there are commas through out the quot;test textquot;
gt; gt;gt; and
gt; gt;gt; quot;this is a testquot; columns therefore text to columns wont work. However
gt; gt;gt; what I
gt; gt;gt; need to show in each separate column is enclosed in quotes.
gt; gt;gt;
gt; gt;gt; Any help would be appreciated!
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
--
Dave Peterson
Now, that's interesting. The test strings I used were numerical, and looked
like the line below:
quot;1234,5678,9quot; 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.
quot;Dave Petersonquot; gt; wrote in message
...
gt;I don't have xl2k anymore, but that's not the way I remember it working.
gt;
gt; I put:
gt;
gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt;
gt; in a text file and xl2003 imported it nicely (test, text was one field).
gt;
gt; Can you try it one more time?
gt;
gt; Doug Kanter wrote:
gt;gt;
gt;gt; In Excel 2000, it doesn't quot;respectquot; the commas. The OP has text strings
gt;gt; with
gt;gt; commas within them. Excel interprets them as delimiters, regardless of
gt;gt; the
gt;gt; quotes.
gt;gt;
gt;gt; quot;Dave Petersonquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; When you do File|Open, you should be able to specify Delimited (by a
gt;gt; gt; comma).
gt;gt; gt; Excel will respect those strings within double quotes.
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; naulerich wrote:
gt;gt; gt;gt;
gt;gt; gt;gt; I have a .txt file that looks like:
gt;gt; gt;gt;
gt;gt; gt;gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt;gt; gt;gt;
gt;gt; gt;gt; I want my output in excel to be the following:
gt;gt; gt;gt;
gt;gt; gt;gt; A1=123
gt;gt; gt;gt; B1=test text
gt;gt; gt;gt; C1=this is a test
gt;gt; gt;gt; D1=abc
gt;gt; gt;gt;
gt;gt; gt;gt; Potential problem is that there are commas through out the quot;test textquot;
gt;gt; gt;gt; and
gt;gt; gt;gt; quot;this is a testquot; columns therefore text to columns wont work. However
gt;gt; gt;gt; what I
gt;gt; gt;gt; need to show in each separate column is enclosed in quotes.
gt;gt; gt;gt;
gt;gt; gt;gt; Any help would be appreciated!
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt;
gt;gt; gt; Dave Peterson
gt;
gt; --
gt;
gt; Dave Peterson
I am using 2003 it is not respecting the quot;quot;...I am going to have the souce
data delimiter changes to a ~ and see if that helps.
Thanks!
quot;Dave Petersonquot; wrote:
gt; I don't have xl2k anymore, but that's not the way I remember it working.
gt;
gt; I put:
gt;
gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt;
gt; in a text file and xl2003 imported it nicely (test, text was one field).
gt;
gt; Can you try it one more time?
gt;
gt; Doug Kanter wrote:
gt; gt;
gt; gt; In Excel 2000, it doesn't quot;respectquot; the commas. The OP has text strings with
gt; gt; commas within them. Excel interprets them as delimiters, regardless of the
gt; gt; quotes.
gt; gt;
gt; gt; quot;Dave Petersonquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; When you do File|Open, you should be able to specify Delimited (by a
gt; gt; gt; comma).
gt; gt; gt; Excel will respect those strings within double quotes.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; naulerich wrote:
gt; gt; gt;gt;
gt; gt; gt;gt; I have a .txt file that looks like:
gt; gt; gt;gt;
gt; gt; gt;gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt; gt;gt;
gt; gt; gt;gt; I want my output in excel to be the following:
gt; gt; gt;gt;
gt; gt; gt;gt; A1=123
gt; gt; gt;gt; B1=test text
gt; gt; gt;gt; C1=this is a test
gt; gt; gt;gt; D1=abc
gt; gt; gt;gt;
gt; gt; gt;gt; Potential problem is that there are commas through out the quot;test textquot;
gt; gt; gt;gt; and
gt; gt; gt;gt; quot;this is a testquot; columns therefore text to columns wont work. However
gt; gt; gt;gt; what I
gt; gt; gt;gt; need to show in each separate column is enclosed in quotes.
gt; gt; gt;gt;
gt; gt; gt;gt; Any help would be appreciated!
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt;
gt; --
gt;
gt; Dave Peterson
gt;
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:
gt;
gt; I am using 2003 it is not respecting the quot;quot;...I am going to have the souce
gt; data delimiter changes to a ~ and see if that helps.
gt;
gt; Thanks!
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; I don't have xl2k anymore, but that's not the way I remember it working.
gt; gt;
gt; gt; I put:
gt; gt;
gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt;
gt; gt; in a text file and xl2003 imported it nicely (test, text was one field).
gt; gt;
gt; gt; Can you try it one more time?
gt; gt;
gt; gt; Doug Kanter wrote:
gt; gt; gt;
gt; gt; gt; In Excel 2000, it doesn't quot;respectquot; the commas. The OP has text strings with
gt; gt; gt; commas within them. Excel interprets them as delimiters, regardless of the
gt; gt; gt; quotes.
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; When you do File|Open, you should be able to specify Delimited (by a
gt; gt; gt; gt; comma).
gt; gt; gt; gt; Excel will respect those strings within double quotes.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; naulerich wrote:
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; I have a .txt file that looks like:
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; I want my output in excel to be the following:
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; A1=123
gt; gt; gt; gt;gt; B1=test text
gt; gt; gt; gt;gt; C1=this is a test
gt; gt; gt; gt;gt; D1=abc
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; Potential problem is that there are commas through out the quot;test textquot;
gt; gt; gt; gt;gt; and
gt; gt; gt; gt;gt; quot;this is a testquot; columns therefore text to columns wont work. However
gt; gt; gt; gt;gt; what I
gt; gt; gt; gt;gt; need to show in each separate column is enclosed in quotes.
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; Any help would be appreciated!
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;
--
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.~ lt;lt;internal source groupgt;gt;~ 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.~ lt;lt;internal source groupgt;gt;~ Nice
to Have~ ~
quot;Dave Petersonquot; wrote:
gt; I think you should copy a few of the lines from the text file into your followup
gt; post--if you don't get it working.
gt;
gt;
gt;
gt; naulerich wrote:
gt; gt;
gt; gt; I am using 2003 it is not respecting the quot;quot;...I am going to have the souce
gt; gt; data delimiter changes to a ~ and see if that helps.
gt; gt;
gt; gt; Thanks!
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; I don't have xl2k anymore, but that's not the way I remember it working.
gt; gt; gt;
gt; gt; gt; I put:
gt; gt; gt;
gt; gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt; gt;
gt; gt; gt; in a text file and xl2003 imported it nicely (test, text was one field).
gt; gt; gt;
gt; gt; gt; Can you try it one more time?
gt; gt; gt;
gt; gt; gt; Doug Kanter wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; In Excel 2000, it doesn't quot;respectquot; the commas. The OP has text strings with
gt; gt; gt; gt; commas within them. Excel interprets them as delimiters, regardless of the
gt; gt; gt; gt; quotes.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; When you do File|Open, you should be able to specify Delimited (by a
gt; gt; gt; gt; gt; comma).
gt; gt; gt; gt; gt; Excel will respect those strings within double quotes.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; naulerich wrote:
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; I have a .txt file that looks like:
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; I want my output in excel to be the following:
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; A1=123
gt; gt; gt; gt; gt;gt; B1=test text
gt; gt; gt; gt; gt;gt; C1=this is a test
gt; gt; gt; gt; gt;gt; D1=abc
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; Potential problem is that there are commas through out the quot;test textquot;
gt; gt; gt; gt; gt;gt; and
gt; gt; gt; gt; gt;gt; quot;this is a testquot; columns therefore text to columns wont work. However
gt; gt; gt; gt; gt;gt; what I
gt; gt; gt; gt; gt;gt; need to show in each separate column is enclosed in quotes.
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; Any help would be appreciated!
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;
It appears you've changed commas to tilde signs. This that make the import
process work correctly?quot;naulerichquot; gt; wrote in message
...
gt; Here is a sample of the data, thanks so much!
gt;
gt; 5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
gt; utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
gt; MSP, electronic secondary, and using the latest claims submission formats
gt; we
gt; must prep for migrating sites to the latest 4E format. This requirement is
gt; in
gt; sync with the EDI requirement with the same name. This is a multi-step
gt; project.~ Today most of our sites are utilizing the WEBMD_NSF Format. To
gt; ensure that all sites are prepared for MSP, electronic secondary, and
gt; using
gt; the latest claims submission formats we must prep for migrating sites to
gt; the
gt; latest 4E format. This requirement is in sync with the EDI requirement
gt; with
gt; the same name. This is a multi-step project.~ lt;lt;internal source groupgt;gt;~
gt; Have
gt; to Have~ ~
gt;
gt; 5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
gt; currently work in Patient Check-in. So unless the front desk brings up the
gt; detail on the patient, alerts are not presented to the user causing them
gt; to
gt; miss critical items at the first contact with the patient.
gt;
gt; By presenting this alert when the patient is chosen on the check in list,
gt; the user will make or take the proper action necessary based on the alert
gt; message.~ Need to activate Patient and Account Alert during patient
gt; check-in.
gt; Not currently working in Patient Check-in.~ lt;lt;internal source groupgt;gt;~
gt; Nice
gt; to Have~ ~
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt;gt; I think you should copy a few of the lines from the text file into your
gt;gt; followup
gt;gt; post--if you don't get it working.
gt;gt;
gt;gt;
gt;gt;
gt;gt; naulerich wrote:
gt;gt; gt;
gt;gt; gt; I am using 2003 it is not respecting the quot;quot;...I am going to have the
gt;gt; gt; souce
gt;gt; gt; data delimiter changes to a ~ and see if that helps.
gt;gt; gt;
gt;gt; gt; Thanks!
gt;gt; gt;
gt;gt; gt; quot;Dave Petersonquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; I don't have xl2k anymore, but that's not the way I remember it
gt;gt; gt; gt; working.
gt;gt; gt; gt;
gt;gt; gt; gt; I put:
gt;gt; gt; gt;
gt;gt; gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt;gt; gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt;gt; gt; gt; quot;123quot;,quot;test, textquot;,quot;this is a testquot;,quot;abcquot;
gt;gt; gt; gt;
gt;gt; gt; gt; in a text file and xl2003 imported it nicely (test, text was one
gt;gt; gt; gt; field).
gt;gt; gt; gt;
gt;gt; gt; gt; Can you try it one more time?
gt;gt; gt; gt;
gt;gt; gt; gt; Doug Kanter wrote:
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; In Excel 2000, it doesn't quot;respectquot; the commas. The OP has text
gt;gt; gt; gt; gt; strings with
gt;gt; gt; gt; gt; commas within them. Excel interprets them as delimiters, regardless
gt;gt; gt; gt; gt; of the
gt;gt; gt; gt; gt; quotes.
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; quot;Dave Petersonquot; gt; wrote in message
gt;gt; gt; gt; gt; ...
gt;gt; gt; gt; gt; gt; When you do File|Open, you should be able to specify Delimited
gt;gt; gt; gt; gt; gt; (by a
gt;gt; gt; gt; gt; gt; comma).
gt;gt; gt; gt; gt; gt; Excel will respect those strings within double quotes.
gt;gt; gt; gt; gt; gt;
gt;gt; gt; gt; gt; gt;
gt;gt; gt; gt; gt; gt;
gt;gt; gt; gt; gt; gt; naulerich wrote:
gt;gt; gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt; gt;gt; I have a .txt file that looks like:
gt;gt; gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt; gt;gt; quot;123quot;,quot;test textquot;,quot;this is a testquot;,quot;abcquot;
gt;gt; gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt; gt;gt; I want my output in excel to be the following:
gt;gt; gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt; gt;gt; A1=123
gt;gt; gt; gt; gt; gt;gt; B1=test text
gt;gt; gt; gt; gt; gt;gt; C1=this is a test
gt;gt; gt; gt; gt; gt;gt; D1=abc
gt;gt; gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt; gt;gt; Potential problem is that there are commas through out the quot;test
gt;gt; gt; gt; gt; gt;gt; textquot;
gt;gt; gt; gt; gt; gt;gt; and
gt;gt; gt; gt; gt; gt;gt; quot;this is a testquot; columns therefore text to columns wont work.
gt;gt; gt; gt; gt; gt;gt; However
gt;gt; gt; gt; gt; gt;gt; what I
gt;gt; gt; gt; gt; gt;gt; need to show in each separate column is enclosed in quotes.
gt;gt; gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt; gt;gt; Any help would be appreciated!
gt;gt; gt; gt; gt; gt;
gt;gt; gt; gt; gt; gt; --
gt;gt; gt; gt; gt; gt;
gt;gt; gt; gt; gt; gt; Dave Peterson
gt;gt; gt; gt;
gt;gt; gt; gt; --
gt;gt; gt; gt;
gt;gt; gt; gt; Dave Peterson
gt;gt; gt; gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Dave Peterson
gt;gt;
留言列表
