How to prepare data in Microsoft Excel

Microsoft Excel and other spread sheet programs are knwon to be able to read many data types, however, sometimes this data is interpreted differently that desired. Below are tips to convert/ process data in spread sheets after import and prior to further processing in the Tygron Platform.

Importing text as numbers in the legacy Import Text wizard in Office 2019 and up

In newer Office versions from 2019 and up, the import text options are changed. The new wizard only has a limmited set of options. To use the legacy wizard, this needs to be activated first in the newer Office versions.

See the following article on how to activate the legacy text import wizard: https://support.office.com/en-us/article/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857

Converting text to numbers

When imported data in an excel has the columns with number values alligned to the right, this usually means the numbers are interpreted as text. In the following article from Microsoft it is explained how to easily convert columns with text to numbers: https://support.office.com/en-us/article/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885

Removing spaces and other characters in bulk from ranges of cells

Sometimes extra redundant spaces or other characters are found in (imported) cells that hinder proper interpretation of the cell value. By using the search and replace functionality this can be dealt with.

How to remove redundant spaces from imported data in Microsoft excel:

1. Select the cell (range) affected
2. Select Start
3. Select Edit > Replace
4. Enter a space in the search field
5. Leave the replace field empty
6. Select Replace All

• 

  1. Select the cell (range) affected

• 

  2. Select Start

• 

  3. Select Edit > Replace

• 

  4. Enter a space in the search field

• 

  5. Leave the replace field empty

• 

  6. Select Replace All