/build/static/layout/Breadcrumb_cap_w.png

Text to excel

Hey IT folks!

Hope you can help me with a rather messy text file that I am trying to import to excel.. 

The text file looks like this:
$$$
ankle swelling
HISTORY:    Trauma
COMPARISON:    []
Technique: AP, lateral and oblique views

FINDINGS: There is no evidence of acute fracture or dislocation. There is lateral soft tissue swelling seen. No evidence of any bone destruction.
IMPRESSION: Soft tissue swelling but no evidence of obvious acute fracture is demonstrated.

$$$
basic
INDICATIONS: <<Clinical Information>>
COMPARISON: [< None. >]
FINDINGS: [  ]
$$$

I would like it to go into excel as this

ColumnA   ColumnB   ColumnC
ankle swelling
   HISTORY   Trauma
ankle swelling
   COMPARISON:   None
ankle swelling   Technique:    AP, lateral and oblique views
ankle swelling   FINDINGS:   There is no evidence of acute fracture or dislocation. There is lateral soft tissue swelling seen. 
basic
   INDICATIONS:   <<Clinical Information>>
basic
   COMPARISON:   [< None. >]
basic   FINDINGS:   [  ]

the only things that are consistent that would identify each column are: 
  • The start of new title (columnA) is immediately after $$$ this indicates start of new record
  • Each of the sub titles (columnB) ends in ":" 
  • Each of the descriptions (columnC) starts after the ":" 

    Any one has any ideas? VBA script maybe? but i wouldn't know where to begin!
Thanks in advance.

Asked 9 years ago 2016 views
4 Comments   [ + ] Show comments
  • Is there a reliable way of determining the end of a record? Is there a $$$ at the end of a single or series of records? VBscript would indeed work to convert the existing layout into something like a comma delimited text file which would then import directly into Excel. However, as I'm getting a strong indication that you are not a programmer, how do you plan to maintain and develop any program that someone in this forum may offer? - EdT 9 years ago
  • Hey EdT, thanks for your reply, I don't know enough about VBscripts, this is a new task that come up and maybe the case I need to start learning about VB in more detail, the $$$ dollar is at the end of each series of records, the records within the series has ":" i am only interested in what is before and after the ":" within each series of records. hope this helps? - HiiShino 9 years ago
  • Google "Excel text to columns" you should be able to import the text files using that method. - KHaught 9 years ago
    • lol Thanks KHaught! I know how to do text columns but what about the getting into the format i describe above? - HiiShino 9 years ago
  • Once this is imported with a "from text" import delimited by your $ to show a new record, it should be easy to split the text the way you want using text to columns delimited on your : I was able to do it in a few minutes in excel. You could then easily store it in a macro to be run against any other imported text in the future. The other option would be to split the text using formulas such as this -
    =LEFT(A12,(FIND(":",A12,1)-1)) assuming A12 contained this string from your example -
    INDICATIONS: <<Clinical Information>>

    Same could be used to find the split in any of your cells which contain the : delimiter. - KHaught 9 years ago

Answers (0)

Be the first to answer this question

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ