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
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.

INDICATIONS: <<Clinical Information>>
COMPARISON: [< None. >]

I would like it to go into excel as this

ColumnA   ColumnB   ColumnC
ankle swelling
  HISTORY   Trauma
ankle swelling
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. 
  INDICATIONS:   <<Clinical Information>>
  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.

4 Comments   [ - ] Hide 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?
  • 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?
  • Google "Excel text to columns" you should be able to import the text files using that method.
    • lol Thanks KHaught! I know how to do text columns but what about the getting into the format i describe above?
  • 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.
Please log in to comment

There are no answers at this time
Answer this question or Comment on this question for clarity