# Questions? We have answers.

## Summary

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

### Overview video

[Watch on Vimeo](https://vimeo.com/328677654?fl=pl&fe=vl)

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

### 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**.

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.

Alternatively, use the **Date** delimiter (in step 3 of 3). Be sure the date format in the menu matches your file's date format.

4. To pull the date back together, paste the **following formula** into an empty column:

**=DATE(Year Column, Month Column, Day Column)**

5. Go to the **Number** tab in the Home ribbon.
6. Use the menu to select **Short Date.**

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

## 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**.

This will remove special characters (such as "$") from your columns.

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

5. In the Visual Basic editor, select Insert > **Module**.

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
```

## 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

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

01. Select the column(s) you want to fill down.
02. Open the Editing tab in the Home ribbon.
03. Open the **Find & Select** menu.
04. Select **Go To Special** **...**

****
05. Select **Blanks**.
06. >Select > **Ok**.

This selection highlights the empty cells and makes the first empty cell white.

07. Press **F2** on your keyboard to enter values into the white cell.
08. When the type indicator blinks, enter **=** then **the** **cell above** (for example, " **=K1**").

09. 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](https://support.mindbridge.ai/hc/en-us/articles/4410728102423) within MindBridge.

## Additional considerations

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

- Trial Balance files should always contain unique accounts, as redundant accounts will cause errors. [Learn how to resolve this issue](https://support.mindbridge.ai/hc/en-us/articles/1500007439741).
- It is always recommended to import an " **ungrouped**" file format into MindBridge. [Learn about grouped versus ungrouped file formats](https://support.mindbridge.ai/hc/en-us/articles/4402388167959).
- If you want to filter your analysis by the values found in a particular column, add that column during column mapping. [Learn how to add a column for filtering.](https://support.mindbridge.ai/hc/en-us/articles/360058269853)

**Note:** Visit our [quick reference guide for advanced data formatting](https://support.mindbridge.ai/hc/en-us/articles/1500008298641) for tips and tricks you can leverage to format your files or manipulate columns.