Questions? We have answers.

Data formatting tips and tricks

Article author
Jonathon Plowman-Samson
  • Updated

Summary

This article will show the data formatting tricks that can be used to format your file for the ingestion process.


Date format

MindBridge is good at translating all kinds of date formats; as long as Excel recognized 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's date.
    The date format should be grouped as appears in the image below.
    image_-_2021-01-07T133432.841.png
 

Re-format the date

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

  3. 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 delimit the date.
    image_-_2021-01-07T133546.092.png

    Alternatively, use the Date delimiter (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 to in 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
 

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

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


Format amount or debit and credit values

The amount or debit and credit columns should not contain any special characters. The easiest way to ensure this is to format the columns to a 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 type from an account number
- or-
Separate the account description from account number

  1. Insert a column after the column you are working with and re-name it. 
  2. Go to the Developer tab.
  3. Open the Visual Basic editor.
    image_-_2021-01-07T133841.317.png

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

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

If you want to speed up the data formatting and ingestion process, there are a few things you can do.

  • Save the file as a .CSV file. This decreases the file size, making it 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 which 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.


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, learn how to merge multiple files onto one sheet within MindBridge.

 


Anything else on your mind?

Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.


Related articles

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.