• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Numerical Text

Hi,

I have an excel file into which data appears to be numbered but in actual not working like a number

Thanks in advance!!

Regards
Neeraj Kumar Agarwal
 

Attachments

  • Excel Query.xlsx
    10 KB · Views: 11
You have a character code 160 (a non-breaking space) at the end of each cell.
You can remove them as follows:
  1. Select the offending cells
  2. Press Ctrl+h on the keyboard to bring up the Find and Replace dialogue box, make sure that it looks like this:
    upload_2018-2-23_12-33-48.png
  3. Make sure both Find what: and Replace with: fields are completely empty
  4. Put the cursor in the Find what: field then holding down the Alt key, using the NUMERIC keypad, type 0160, release the Alt key
  5. You won't see much in that field but a single character should be there
  6. Click Replace All.

QED
 
You can also do this by selecting the cells and running this single line macro:
Code:
Sub blah()
    Selection.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
 
2 more alternatives.

1. Use Text To Columns, using ALT + 0160 as delimiter.
2. Use formula in column B. =SUBSTITUTE(A2,CHAR(160),"")+0
 
Use the formula method. Then copy paste as value only over the original. Delete formula column as needed.

Aternately, in some out of way cell enter =CHAR(160) and copy value only to another cell. Double click on that cell and copy content. Paste that into your Search & Replace or Text to Column field as needed.
 
Thanks to all!! But I am having lenovo laptop with no numlock key. Could any way out be suggested
Edit one of the offending cells, select that last character and Ctrl+c to copy it to the clipboard, then open the find and replace dialogue and paste the clipboard into the Find field with Ctrl+v.

What model laptop?
 
Back
Top