Questions? We have answers.

Data formatting tips and tricks

  • Updated

Summary

Learn a few data formatting tricks that can be used to format your file before importing it into MindBridge.


Overview video


Date format

MindBridge can translate all kinds of date formats, as long as Excel recognizes the data as a date, so will MindBridge.

Check to see if your date is properly formatted

  1. Apply a filter to the data in Excel.
  2. Use the Filter by Date menu to see how Excel interprets the data
    The date format should be grouped as appears in the image below.
    image_-_2021-01-07T133432.841.png

Re-format the date

  1. If data requires re-formatting, select the Text to Columns feature in Excel, found in the Data tab.
  2. Select Delimited then click Next.
    image_-_2021-01-07T133523.729.png

  3. In step 2 of 3, in the Delimiter section, check the box next to Other, then enter " / " (a forward slash) into the text field. 
    Note: When using a custom delimiter, be sure to insert the appropriate amount of new columns after the original date column in advance. These new columns will be used to delimit the date.
    image_-_2021-01-07T133546.092.png

    Alternatively, use the Date delimiter (in step 3 of 3). Be sure the date format in the menu matches your file's date format.
    image_-_2021-01-07T133602.726.png

  4. To pull the date back together, paste the following formula into an empty column:
    =DATE(Year Column, Month Column, Day Column)image_-_2021-01-07T133619.653.png

  5. Go to the Number tab in the Home ribbon.
  6. Use the menu to select Short Date.  
    image_-_2021-01-07T133642.965.png
 

Below is a before and after image displaying the preferred date format of YYYY-MM-DD.

image_-_2021-01-07T133707.746.png


Format amount or debit and credit values

The amount column, or debit and credit columns, must not contain any special characters. The easiest way to ensure this is to change the column(s) into number format.

  1. From the Number tab in the Home ribbon, use the menu to select Number.
    image_-_2021-01-07T133728.736.png

    This will remove special characters (such as "$") from your columns. 
    image_-_2021-01-07T133753.518.png
 

Extract a transaction ID from an account number
- or-
Separate the account description from the account number

  1. Insert a column after the column you are working with and re-name it.
  2. There are two ways to separate the data into two fields: 
    1. Use Text to Columns by following the above steps to separate the data if delimiters are used. 
    2. Use Visual Basic to extract the data by following step 3 and onwards.
  3. Go to the Developer tab.
  4. Open the Visual Basic editor.
    image_-_2021-01-07T133841.317.png

  5. In the Visual Basic editor, select Insert > Module.
    image_-_2021-01-07T133857.680.png

  6. Copy and paste the text below into the white space that appears and select Save (if a prompt appears, select Yes).
    Function TextOnly(pWorkRng As Range) As String
    'Updateby20140515
    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)
    End If
    Next
    TextOnly = OutValue
    End Function
    image_-_2021-01-07T133915.719.png


File formatting

Large file sizes take a while to import into MindBridge, but there are a few things you can do to speed things up.

Save the file as a .csv

Saving the file with the extension .csv decreases the file size so it's easier to format and import into MindBridge.

Remove empty rows


image_-_2021-01-07T133934.378.png

Rename the headers to align with the following MindBridge headers. This will help speed up the import process.

  • Account ID
  • Account Name
  • Posted Date
  • Memo
  • Transaction ID
  • Transaction Type (if you have it)
  • Amount (or Debt and Credit)
  • Source (if you have it)
  • User ID (if you have it)


Fill down on account numbers and names (grouped files)

  1. Select the column(s) you want to fill down.
  2. Open the Editing tab in the Home ribbon.
  3. Open the Find & Select menu.
  4. Select Go To Special...
    image_-_2021-01-07T133958.240.png

  5. Select Blanks.
  6. Select Ok.
    image_-_2021-01-07T134026.436.png

    This selection highlights the empty cells and makes the first empty cell white.
    image_-_2021-01-07T134045.556.png

  7. Press F2 on your keyboard to enter values into the white cell.
  8. When the type indicator blinks, enter = then the cell above (for example, "=K1").
    image_-_2021-01-07T134103.823.png
  9. Press Ctrl+Enter on your keyboard.
    All of your cells should now be filled.
  10. Copy and paste the values in that cell to remove the formula.


Concatenating files

If the file is within the limits of Excel, simply copy and paste the files together without the headers from the second file into one sheet. 

If the file exceeds the limits of Excel, learn how to merge multiple files onto one sheet within MindBridge.


Additional considerations

When formatting your data, pay attention to these key considerations:

Note: Visit our quick reference guide for advanced data formatting for tips and tricks you can leverage to format your files or manipulate columns.

Anything else on your mind? Chat with us or submit a request for further assistance.

Was this article helpful?