How to prepare data in Microsoft Excel: Difference between revisions

From Tygron Support wiki
Jump to navigation Jump to search
← Older edit
Frank@tygron.nl (talk | contribs)
Tygron, Tygron_admin, Bots, Bureaucrats, Interface administrators, Administrators
45,539 edits
No edit summary
 
(9 intermediate revisions by 2 users not shown)
Line 1: Line 1:
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.
On this page some general information is given about how to convert data in a spreadsheet.
Microsoft Excel and other spread sheet programs are known to be able to read many data types, however, sometimes this data is interpreted differently than 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==
==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 limited set of options. To use the legacy wizard, this needs to be activated first in the newer Office versions.  
Office 2019 and later have limited text import options; you must activate the legacy wizard first.


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 Text Import Wizard]
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 Text Import Wizard]


==Converting text to numbers==
==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:
If imported Excel columns have numbers aligned to the left, they are likely being 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 Convert numbers stored as text to numbers]
[https://support.office.com/en-us/article/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885 Convert numbers stored as text to numbers]
[[File:Aligned_to_the_left.png]]


==Removing spaces and other characters in bulk from ranges of cells==
==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.
Redundant spaces or characters in imported cells can hinder data interpretation; use 'Find and Replace' to remove them.


{{Editor steps|title=remove redundant spaces from imported data in a work book or spread sheet|Select the cell (range) affected|Select Start|Select Edit > Replace|Enter a space in the search field|Leave the replace field empty|Select Replace All}}
{{Editor steps|title=remove redundant spaces from imported data in a work book or spread sheet|Select the cell (range) affected|Select Start|Select Edit > Replace|Enter a space in the search field|Leave the replace field empty|Select Replace All}}
Line 23: Line 26:
File:ExcelReplaceAllBtn1.jpg|6. Select Replace All
File:ExcelReplaceAllBtn1.jpg|6. Select Replace All
</gallery>
</gallery>
{{article end
|seealso=
* [[Excel]]
* [[Var (Excel)]]
}}
[[Category:How-to's]]

Latest revision as of 09:35, 8 July 2026

On this page some general information is given about how to convert data in a spreadsheet. Microsoft Excel and other spread sheet programs are known to be able to read many data types, however, sometimes this data is interpreted differently than 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

Office 2019 and later have limited text import options; you must activate the legacy wizard first.

See the following article on how to activate the legacy text import wizard:Text Import Wizard

Converting text to numbers

If imported Excel columns have numbers aligned to the left, they are likely being interpreted as text. In the following article from Microsoft it is explained how to easily convert columns with text to numbers: Convert numbers stored as text to numbers


Removing spaces and other characters in bulk from ranges of cells

Redundant spaces or characters in imported cells can hinder data interpretation; use 'Find and Replace' to remove them.

How to remove redundant spaces from imported data in a work book or spread sheet:
  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
    1. Select the cell (range) affected
  • 2. Select Start
    2. Select Start
  • 3. Select Edit > Replace
    3. Select Edit > Replace
  • 4. Enter a space in the search field
    4. Enter a space in the search field
  • 5. Leave the replace field empty
    5. Leave the replace field empty
  • 6. Select Replace All
    6. Select Replace All

See also

Retrieved from "https://support.tygron.com/w/index.php?title=How_to_prepare_data_in_Microsoft_Excel&oldid=90081"