This article will show the data formatting tricks that can be used to format your file for the ingestion process.
Currently, the easiest date format is YYYY-MM-DD.
How do you know if your date is properly formatted?
Apply a filter to the data in Excel.
Use the Filter by Date drop-down to see how Excel interprets the data's date.
The image below is the wrong format:
The date format should look like the screenshot below:
How do I re-format the date?
- Use the Text to Columns feature in Excel, which is found in the Data tab.
Check the Delimited radio button and click Next.
You can use the Custom Delimiter (step 2 of 3) and choose "/" in the Other: text field.
Note: When using the Custom Delimiter, be sure to insert the appropriate amount of new rows after the original date column. These new columns will be used to deliminate the date.
You can alternatively Use the Date delimiter (step 3 of 3). Be sure the date format in the drop-down matches your file's date format.
To pull the date back together, paste the following formula to in an empty column:
=DATE(Year Column, Month Column, Day Column)
Once the date is properly formatted, always ensure that it is formatted to Short Date. This will ensure that there can be no reason for an error.
From the Number tab in the Home Ribbon, use the drop-down to select Short Date.
Here is a before and after image:
Amount or Debit and Credit Values
The amount or debit and credit columns shouldn’t contain any special characters. The best way to ensure this is to format the columns to a number format.
From the Number tab in the Home Ribbon, use the drop-down to select Number.
This will remove special characters (example: '$') from your columns.
Extracting a Transaction Type from an Account Number
Separating the Account Description from Account Number:
Insert a Column after the column you are working with and re-name it.
Then go to the Developer tab and open the Visual Basic Editor.
Once in the Visual Basic Editor, Click Module, which is under Insert in the bar.
Copy and Paste the text below into the white space that appears and click Save (if any prompt pops up, just click “Yes”).
Function TextOnly(pWorkRng As Range) As String
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
For xIndex = 1 To VBA.Len(xValue)
If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then
OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
TextOnly = OutValue
If you want to speed up the data formatting and ingestion process, you can always open the file and “Save As” as .csv. This will make the file smaller and easier to format, and it will ingest quicker.
Some other quick tips
- Remove any empty rows:
Rename the headers to align with the following MindBridge headers. This which will help speed up the ingestion process:
- Account ID
- Account Name
- Posted Date
- Transaction ID
- Transaction Type (if you have it)
- Amount (or Debt and Credit)
- Source (if you have it)
- User ID (if you have it)
Filling Account Numbers and Names Down (Grouped Files)
Begin by selecting the column(s) you want to fill down.
From the Editing tab in the Home ribbon, Click Find & Select to open the context menu and then click Go To Special.
Check the Blanks checkbox and click Ok. This selection will highlight the empty cells and make the with the top empty white.
This selection will highlight the empty cells and make the first empty cell white.
Press F2 on your keyboard to enter values into the white cell.
Once the curser is blinking in the white cell enter “=the cell above” (example: =K1).
Then press “CTRL+Enter” on your keyboard.
All of your cells should now be filled! Copy and paste the values in that cell to remove the formula.
Concatenation of Files
If the file is within the limits of Excel, simply copy and paste the files together without the headers from the second file.
If the file exceeds the limits of Excel, contact your Customer Success Manager at MindBridge and they will help you out.