'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Wrong Data Type Error After Pasting Database Data
Question details
The user is experiencing a 'wrong data type' error in Excel concatenation formulas after pasting data from a database report, and the 'Paste as Text' option is unexpectedly missing.
- Product
- Microsoft 365 Excel
- Device & OS
- Windows 11
- Scenario
- Pasting exported database report data into a spreadsheet to be used in concatenation formulas.
- Observed behavior
- Excel formulas return 'a value used in the formula is the wrong data type', and the standard 'Paste as Text' option is no longer available in the paste menu.
Verify whether you are copying an entire cell or just the text contents from the formula bar, as this changes which paste options Excel makes available.
Disable Automatic Data Transformations in Excel Settings
Prevent Excel from automatically changing the format of your pasted database text, which often resolves unexpected formula data type errors.
Microsoft 365 Excel includes a feature that automatically converts specific text patterns into data types. When pasting database reports, this automatic conversion can alter text into formats that are incompatible with concatenation formulas, resulting in the wrong data type error.
Click the 'File' tab in the top-left corner of Microsoft Excel, then select 'Options' at the bottom of the left sidebar.
In the Excel Options dialog box, click on 'Data' in the left-hand navigation pane.
Scroll down to the 'Automatic Data Conversion' section. Uncheck the options that enable default data transformations for text entered, pasted, or imported into Excel.
Click 'OK' to save your new settings, then try pasting your database report data again to see if your concatenation formulas function normally.
Verify Cell Edit Mode and Restore Paste Options
If the 'Paste as Text' option is missing, it is usually because the destination cell is currently in edit mode. Exiting edit mode restores the standard paste options menu.
Switch to WPS Office for Clean Data Handling
If Excel's automatic formatting continues to disrupt your workflow, consider using WPS Office. WPS Spreadsheet handles database exports cleanly without forcing unwanted data type conversions, keeping your concatenation formulas working flawlessly.
- 1. Open Your File: Launch WPS Spreadsheet and open your existing Excel workbook.
- 2. Paste Unformatted Text: Right-click the target cell, choose 'Paste Special', and select 'Unformatted Text' to safely input your database report data.
- 3. Run Formulas: Your concatenation formulas will immediately process the raw text without returning data type errors.
Frequently Asked Questions
Why does my Excel concatenation formula show a wrong data type error?
This error typically occurs when a cell referenced in your concatenation formula contains an incompatible data type, such as an error value (#VALUE!) or data that Excel has automatically transformed into an unrecognized format during pasting.
Where did the 'Paste as Text' option go in Microsoft 365?
The 'Paste as Text' option usually disappears if you double-click into a cell, which puts it into edit mode. To restore the option, press the 'Esc' key to exit edit mode, then right-click the cell itself rather than clicking inside it.
How do I stop Excel from automatically reformatting my pasted text?
In newer versions of Microsoft 365 Excel, you can prevent automatic reformatting by going to File > Options > Data and disabling the automatic data conversions settings for text entered, pasted, or imported.
