How to Import OFX Files Into Microsoft Excel

Medioimages/Photodisc/Photodisc/Getty Images

Many online banks distribute their online statements in the OFX file format. OFX is a file extension used by some financing programs. If you want to import an OFX file into Microsoft Excel, some extra steps must be taken. OFX files are plain text files with textual commands used to separate entries in the text. To import these to Excel you must first replace the textual commands with a separator that Microsoft Excel can recognise.

Click "Start," type "notepad" (without quotes) and press "Enter." Press "Ctrl + O." Click the "File type" drop-down menu and select "All Files and Folders .." Locate your OFX file, select it and click "Open."

Browse through your OFX file. You will notice that there is a pattern among all entries. Each OFX file will be different, but in general you will notice fields of data such as times, amounts or comments. There will also be a selection of letters or characters separating each line of data (e.g., hrt]). This "separator" will be the same throughout. Determine what the separator is.

Click "View," then "Find and Replace." In the "Find" field, type out the separator that currently exists in your document.

Enter "|" (bar, not an L) by pressing "Shift + \" in the "Replace" field. Click "Replace All." This will replace all the existing separators and replace them with bar--an Excel compatible separator.

Click "File," then "Save As." Click the "File type" drop-down menu and select "All Files and Folders." Enter "toimport.txt" (without quotes) in the file name and click "Save."

Launch Microsoft Excel. Click the Microsoft Office logo in the ribbon. Click "Open."

Select "Text Files" from the list, then navigate to "toimport.txt" and double-click it to open the file. The Text Import Wizard will open.

Select "Delimited" in the "Original data type" field. Click "Next."

Set the "Delimiters" to "|" ("Shift + \") and click "Next." A preview of your import will appear. Click "Finish" to import your data to Microsoft Excel.

Most recent