• 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.

Non-numeric to numeric [SOLVED]

Kpvaishnav


Firstly, Welcome to the Chandoo.org Forums


Do you mean like :


1. A number like 1013 which is text to a Number 1013 ?

or

2. One thousand and Thirteen to 1013?
 
Type the value 1 in a cell somewhere

Select the cell and Copy, Ctrl C

Select the range of Text Number

Paste Special,

Paste Special,

Select Values & Multiply

Ok
 
Hui said:
Type the value 1 in a cell somewhere

Select the cell and Copy, Ctrl C

Select the range of Text Number

Paste Special,

Paste Special,

Select Values & Multiply

Ok
Click to expand...


Hello Hui,

I am reverting after more than 6 months ! I tried the solution given by you several times but without success... i kept trying thinking , i was doing it wrongly. Now coming back due to continued problem.
One more problem in applying VLOOKUP.While copying and pasting, sometimes, I see a space in front of a label.This does not allow VLOOKUP, unless the space is manually removed.
Please ref. the attached file bringing out both these problem. Could you please help ? Thanks.
 

Attachments

  • Non-numeral.xlsx
    10.5 KB · Views: 8
Hi Kpvaishnav

The problem is the non-printable character before your text, (don't know how you got it there), but to remove them try this:-


Select cell B3 on sheet a

In the Formula Bar select the first non-printable character before "Franklin.........", copy it, then

Select cells B3:B7, then

Go to "Find & Select", Find..., in the "Find what" box Paste, then

Go to "Replace ....", but do not put anything in there, then

Click OK
 
Hi Kpvaishnav,

Adding to this.. you can use following formula also to remove space in at start of text in a cell.

TRIM(CLEAN(SUBSTITUTE(A3,CHAR(160)," ")))

Regards,
I Khan
 
Thank you so much both gentlemen oldchippy and ismailkhan! It works !!

I would also request for help for very old problem in worksheet "b"which I could not solve with the earlier solution given i.e. conversion of non-numeral ( say, 438.07) into a number.
 

Attachments

  • Non-numeral1.xlsx
    9.6 KB · Views: 2
Hi kpvaishnav,

You are having problems

All the numbers in C2:E8 have that character in front of them again???


So to turn them back into numbers, select a cell to the right, say H2, insert this formula

=VALUE(TRIM(SUBSTITUTE(C2,CHAR(160)," ")))

Drag it across to J2, then with the three cells still selected down to J8, then

Select cell H2:J8 copy, select cell C2, Paste Special > Values > OK, then

Delete data in H2:J8
 
Hello oldchippy,

Thank you so very much! Hats off to you all and thanks to chandoo.org for such valuable help to all troubled souls like me! This problem was surprisingly creeping in from a downloaded report. However, it is routine when the report on the screen is copied/pasted. Now I need not worry!
 
You must log in or register to reply here.
Back
Top