Pasted Text in Excel Acting Funny

Fix Excel Numbers That Don't Add Up

Some Microsoft Excel values look like numbers, but don't add up. Or, you can't change the formatting for Excel numbers - it always stays the same. Try the solutions in this article, to fix those broken numbers.

For date formatting problems, see the Excel Dates Fix Format page.

NOTE: For instructions on changing written words to numbers (e.g. from Three to 3), see Words to Numbers in Excel.

Look Like Numbers, But Don't Add Up

If you copy data from another program, or from a text file or a web site, Excel may treat the numbers as text. In Excel, the values could look like numbers, but they don't act like numbers, and don't show a correct total, in a SUM formula, as you can see in the spreadsheet below.

Also, you can't change the number formatting - it always stays the same, no matter how you try to format cells.

In the screen shot above, the values in column C look like numbers, but they don't add up. There are 3 cells with formulas:

• SUM: In cell C7, the total for the addition formula is zero, instead of showing the correct answer.
• COUNTA: At the right, in cell F4, the COUNTA function is used in a formula. This function counts any cells that are not empty. The formula result is 4, which shows that there are 4 entries in that range of cells, C3:C6.
• COUNT: However, the COUNT function in cell F5 show a result of zero. That shows that none of the values in cells C3:C6 are recognized as numbers.

Convert Text to Numbers with Paste Special

For some "text" numbers, you can fix the problem with Paste Special. Watch this short video tutorial, to see the steps, and the written instructions are below. The video transcript is further down the page.

NOTE: If you need to do this frequently, you can use a macro to automate the task.

Video Timeline

• 0:00 Introduction
• 0:42 Check the Cell Contents
• 1:40 Check the Formula Bar
• 2:00 Fix the Numbers
• 3:08 Get the Sample File

How to Fix Text Numbers

To fix numbers that are seen as text, follow these steps:

1. Right-click a blank cell, and click Copy
2. Select the cells that contain the "text" numbers
3. Right-click on one of the selected cells, and click Paste Special
4. The Paste Special dialog box opens
5. In the Paste section, select Values
6. In the Operation section, select Add
7. Click OK

Tip: After the numbers have been fixed, you can apply formatting, by using the Number Format commands on the Ribbon's Home tab.

Convert Text to Numbers With VBA

If you frequently convert text to numbers, you can use a macro, like the one shown below.

Store the following macro in a workbook that is always open, such as your Personal Workbook. Then, add that macro to your Quick Access Toolbar, or to one of the tabs on the Excel Ribbon. To fix "text" numbers, select the cells, and click your macro button.

Sub ConvertToNumbers() Dim rng As Range  'get constants in selected range On Error Resume Next Set rng = Selection _   .SpecialCells(xlCellTypeConstants, 23) On Error GoTo errHandler  If Not rng Is Nothing Then   'copy blank cell outside used range   Cells.SpecialCells(xlCellTypeLastCell) _       .Offset(0, 1).Copy      'add to selected cells    rng.PasteSpecial Paste:=xlPasteValues, _        Operation:=xlPasteSpecialOperationAdd Else   MsgBox "Could not find Constants in selection" End If  exitHandler:   Application.CutCopyMode = False   Set rng = Nothing   Exit Sub errHandler:   MsgBox "Could not change text to numbers"   Resume exitHandler End Sub        

Convert Dates with Replace All

Here is another way to try to fix problem numbers. In this example, the numbers are dates, and Excel does not recognize them as real dates.

As shown in the screenshot below, these dates are formatted with slashes, in the date format: m/d/yy

To fix "text dates" in this format, you can try to convert them to real dates by using the Excel Find and Replace feature, to replace the slashes.

Follow these steps to replace the slashes, which should fix the "text" dates:

1. Select the cells that contain the dates
2. On the Ribbon's Home tab, click Find & Select, then click Replace
• Or, use the keyboard shortcut, Ctrl+H
3. The Find and Replace window opens
• Click the Replace tab, if it is not already selected
4. In the Find what box, type a forward slash:/
5. In the Replace with box, type a forward slash:/
6. Next, click the Replace All button
7. When the message box appears, click OK, to confirm the replacements
8. Click the Close button, to dismiss the Find and Replace window

Tip: After the dates have been fixed, you can apply a different date format to the list. Select all of the fixed date cells, and use the Number Format commands on the Ribbon's Home tab.

Fix Hidden Characters

If you copy data from a website, that data might include hidden characters, such a non-breaking space.

In Excel, a non-breaking space has a character code of 160, and that code is not fixed by some of the number cleanup techniques. Instead, you can search for that character code, and replace it with nothing (an empty string).

Tip: If you need to fix hidden characters frequently, you can use a macro to automate the task, like the macro in the section below

Follow these steps to remove the hidden characters, by using Find and Replace:

1. Select the cells that contain the numbers with hidden characters
2. On the Ribbon's Home tab, click Find & Select (at the far right)
3. In the drop down list, click Replace
4. Click in the Find what box
5. Press and hold the Alt key, and on the number keypad, type 0160
• Nothing will appear in the Find what box, after you type that code
6. For Replace with, leave the box empty
• Hidden characters with a code of 160 will be replaced with nothing (an empty string)
7. Click Replace All
8. Click OK to confirm the replacement
9. Close the Find and Replace window

Fix Hidden Characters With VBA

If you frequently need to remove the hidden non-breaking space character, you can use a macro.

Store the following macro in a workbook that is always open, such as your Personal Workbook. Then, add a button to an existing toolbar, and attach the macro to that button. To fix "text" numbers, select the cells, and click the toolbar button.

Sub CleanCode160() Dim rng As Range Dim arr As Variant Dim i As Long Set rng = Selection 'removes character 160 'non-breaking space 'from selected cells If rng.Cells.Count = 1 Then    ReDim arr(1 To 1, 1 To 1)    arr(1, 1) = rng.Value Else    arr = rng.Value End If  For i = 1 To UBound(arr, 1)    arr(i, 1) = Replace(arr(i, 1), Chr(160), "") Next i  rng.Value = arr  End Sub        

Convert Text to Numbers with Text to Columns

This quick technique, using the Excel Text to Columns feature, can fix some numbers that Excel does not recognize as real numbers.

1. Select the cells that contain the numbers
2. On the Ribbon's Data tab, click Text to Columns
3. The Convert Text to Columns Wizard dialog box opens, showing Step 1
4. In Step 1, select Delimited as the File type
5. Then, click the Finish button

In some cases, that changes the text numbers to real numbers.

If that technique didn't work for your data, you can try one of the other methods on this page.

Convert Currency With Different Separators

If a worksheet has currency in a format that uses different separators, use the Text To Columns command to convert the values.

For example, if the data has numbers shown in German currency -- 987.654,32 -- your Excel settings might not recognize those as real numbers, because of the separators in the numbers.

• German currency uses a period as the Thousands separator, and a comma as the Decimal separator
• USA currency uses a comma as the Thousands separator, and a period as the Decimal separator

Try the following steps, to convert the data to from Germany currency, to US currency -- 987,654.32

1. Select the cells that contain the numbers
2. On the Ribbon's Data tab, click Text to Columns
3. The Convert Text to Columns Wizard dialog box opens, showing Step 1
4. Click Next button, twice (Step 1 and Step 2)
5. In Step 3, click the Advanced button
6. From the Decimal separator drop down, select the separator that is currently used in the values -- "," (comma) in this example
7. From the Thousands separator drop down, select the separator that is currently used in the values -- "." (period) in this example
8. Click OK, then click Finish.

Convert Trailing Minus Signs

If you import numbers that have a trailing minus sign, you can use one of the following techniques to convert them to negative numbers.

Use Text to Columns

Use a Formula

Use a Macro

Text to Columns

With the Text to Columns feature, imported numbers with trailing minus signs can be easily converted to negative numbers.

1. Select the cells that contain the numbers
2. Choose Data>Text to Columns
3. To view the Trailing Minus setting, click Next, click Next
4. In Step 3, click the Advanced button
5. Check the box for 'Trailing minus for negative numbers', click OK
6. Click Finish

Note: If 'Trailing minus for negative numbers' is checked, you can click Finish in Step 1 of the Text to Columns wizard.

Convert Trailing Minus Signs - Formula

Thanks to Bob Ryan, from Simply Learning Excel, who sent this formula to fix imported numbers with trailing minus signs.

Follow these steps to create the formula, shown in the screenshot below:

1. In this example, the first number with a trailing minus sign is in cell A1
2. Select cell B1, and enter this formula:
3. =IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),VALUE(A1))
4. Copy the formula down to the last row of data.

In the formula, the RIGHT function returns the last character in cell A1.

If that character is a minus sign, the VALUE function returns the number value to the left of the trailing minus sign.

The minus sign before the VALUE function changes the value to a negative amount.

Convert Trailing Minus Signs Programmatically

In all versions of Excel, you can use the following macro to convert numbers with trailing minus signs.

Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis, dana2@msn.com ' modified by Tom Ogilvy ' = = = = = = = = = = = = = = = =   Dim rng As Range   Dim bigrng As Range    On Error Resume Next   Set bigrng = Cells _      .SpecialCells(xlConstants, xlTextValues).Cells   If bigrng Is Nothing Then Exit Sub    For Each rng In bigrng.Cells     If IsNumeric(rng) Then       rng = CDbl(rng)     End If   Next End Sub        

Paste as CSV

When importing data, you might be able to prevent copied numbers from being pasted as text, if you paste the data as CSV.

1. Copy the data in the other program
2. Switch to Excel
3. Right-click the cell where the paste will start, and click Paste Special
4. Choose Edit>Paste Special
5. Select CSV, if it appears in the list, and click OK

Transcript: Fix Numbers That Don't Add Up

Here is the full transcript for the Fix Numbers That Don't Add video shown above.

----------------------------

If you import or copy data into Excel, such as a bank statement, sometimes the numbers don't add up correctly. We'll see how to fix that.

Here is a very small sample of a bank account. We've got cheque numbers and the amount of each cheque.

To create a total, I can go to the Home tab, and over at the right, click AutoSum. And usually, that will select any numbers above, but in this case it didn't, so I will manually select those, and press Enter, and it shows zero. So even though I've got hundreds of dollars, it's showing zero.

Check Cell Contents

I'm going to add a couple of other functions on this worksheet, and just find out what's going on in these cells.

In this cell, I'm going to get a count of everything that's in those cells, whether it's text or numbers.

In here, I'm going to use COUNTA equals COUNTA open bracket. Then I'll select the cells that have the numbers, close the bracket, and press Enter.

These four cells have something in them. But how many of those have numbers? And to do that here we use COUNTA.

And in this cell, I'm going to use COUNT, and it only counts numbers. So equals COUNT open bracket, select the four cells again, close the bracket, and press Enter.

We have four cells with something in them, but none of those cells have numbers.

Check Formula Bar

If I look at one of these cells and look up in the Formula Bar,

I can see the number, but in front of the number there's an apostrophe, and that indicates that this is text rather than a number.

So, whatever we've downloaded or copied in from somewhere came in as text.

Fix the Numbers

There is a quick way we can fix this, though. We're going to select a blank cell, and then use paste special to paste it over these numbers, and it will add a zero to everything, which won't have any effect on these values, but will change them from text to numbers with that simple step.

I'll select a blank cell and copy. Then select the cells that I want to fix, and go up to Paste, click the drop-down arrow, and go down to Paste Special. And in here, I want to Add, so I'll select that. Click OK.

And now, these have all changed to numbers, and we can see a total at the bottom.

I could format these so that they all line up nicely. I could format this one, as well. And now, we have a total that's correct and nicely formatted.

'------------

Click here, to back to the Fix Numbers That Don't Add video shown above

Get the Sample File

Download the zipped file with the sample data and macros. The zipped file is in xlsm format, and contains macros, so enable them to test the code.

More Data Entry Tutorials

Data Entry Tips

Excel Dates Fix Format

Fill Blank Cells

Increase Numbers With Paste Special

Add Number to Multiple Cells

Excel Data Entry Videos

williamstentsman65.blogspot.com

Source: https://www.contextures.com/xldataentry03.html

Share this post