Questions? We have answers.

Data Formatting Tips & 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.


Dates

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:

image_-_2021-01-07T133413.461.png

The date format should look like the screenshot below:

image_-_2021-01-07T133432.841.png

How do I re-format the date?

  • Use the Text to Columns feature in Excel, which is found in the Data tab.
image_-_2021-01-07T133452.699.png

Check the Delimited radio button and click Next. 

image_-_2021-01-07T133523.729.png

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.

image_-_2021-01-07T133546.092.png

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.

image_-_2021-01-07T133602.726.png

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

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.  

image_-_2021-01-07T133642.965.png

Here is a before and after image:

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

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.

image_-_2021-01-07T133728.736.png

This will remove special characters (example: '$') from your columns. 

image_-_2021-01-07T133753.518.png

Extracting a Transaction Type from an Account Number
- or-
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.

image_-_2021-01-07T133841.317.png

Once in the Visual Basic Editor, Click Module, which is under Insert in the bar. 

image_-_2021-01-07T133857.680.png

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

'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, 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:
image_-_2021-01-07T133934.378.png

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
  • 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)

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.

image_-_2021-01-07T133958.240.png

Check the Blanks checkbox and click Ok. This selection will highlight the empty cells and make the with the top empty white.

image_-_2021-01-07T134026.436.png

This selection will highlight the empty cells and make the first empty cell white.

image_-_2021-01-07T134045.556.png

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. 

image_-_2021-01-07T134103.823.png

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.


Anything else on your mind?
Log in to MindBridge to chat with us, submit a request, or reach out to your assigned Customer Success Manager.


Related articles

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.