I copy data from one website and paste it to excel sheet. The value which is
copied is like 180.12098400983000, 20.000002300000, 79.120000,
130.25999999999999
like this, i have so many data. I can't do formate cell number check
1234.10. each time. Infact, formate cell number check 1234.10, this process
also not any result.

i want to do it with a click or two.

Someone pls suggest

Say we have copied values and pasted them into column A, from A1 thru A100

In B1 enter:

=ROUND(A1,2) and copy down

You can either use col B or copy col B and paste/special values back onto
column A
--
Gary''s Student - gsnu200805


"Harshad" wrote:

I copy data from one website and paste it to excel sheet. The value which is
copied is like 180.12098400983000, 20.000002300000, 79.120000,
130.25999999999999
like this, i have so many data. I can't do formate cell number check
1234.10. each time. Infact, formate cell number check 1234.10, this process
also not any result.

i want to do it with a click or two.

Someone pls suggest

Dear Gary's Student,
Thank you for quick reply.
i used your formula but the result is #VALUE!

Your suggesion is 100% OK for manually entered values.

if want to experience, just go to http://solvdb.ncms.org/solvdb.htm
Sleclect Option "Select By Property Range",
Enter 30 and 40 in "Boiling Point" blank cell
Hit "Display Chemical Selection"
You will get data, pls copy and paste it in excel sheet.
Now, remeining task u know very well.

Hoping your answer soon.

Thank you



"Gary''s Student" wrote:

Say we have copied values and pasted them into column A, from A1 thru A100

In B1 enter:

=ROUND(A1,2) and copy down

You can either use col B or copy col B and paste/special values back onto
column A
--
Gary''s Student - gsnu200805


"Harshad" wrote:

I copy data from one website and paste it to excel sheet. The value which is
copied is like 180.12098400983000, 20.000002300000, 79.120000,
130.25999999999999
like this, i have so many data. I can't do formate cell number check
1234.10. each time. Infact, formate cell number check 1234.10, this process
also not any result.

i want to do it with a click or two.

Someone pls suggest

I believe your values are coming in as text and not as numbers. Have you
tried using the DATA/GET EXTERNAL DATA/NEW WEB QUERY to get your data from
the website in question instead of using the copy command?
--
Kevin Backmann


"Harshad" wrote:

Dear Gary's Student,
Thank you for quick reply.
i used your formula but the result is #VALUE!

Your suggesion is 100% OK for manually entered values.

if want to experience, just go to http://solvdb.ncms.org/solvdb.htm
Sleclect Option "Select By Property Range",
Enter 30 and 40 in "Boiling Point" blank cell
Hit "Display Chemical Selection"
You will get data, pls copy and paste it in excel sheet.
Now, remeining task u know very well.

Hoping your answer soon.

Thank you



"Gary''s Student" wrote:

Say we have copied values and pasted them into column A, from A1 thru A100

In B1 enter:

=ROUND(A1,2) and copy down

You can either use col B or copy col B and paste/special values back onto
column A
--
Gary''s Student - gsnu200805


"Harshad" wrote:

I copy data from one website and paste it to excel sheet. The value which is
copied is like 180.12098400983000, 20.000002300000, 79.120000,
130.25999999999999
like this, i have so many data. I can't do formate cell number check
1234.10. each time. Infact, formate cell number check 1234.10, this process
also not any result.

i want to do it with a click or two.

Someone pls suggest

Try this

=ROUND(TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32))),2)


On Sep 23, 5:26*am, Harshad wrote:
I copy data from one website and paste it to excel sheet. The value which is
copied is like 180.12098400983000, 20.000002300000, 79.120000,
130.25999999999999
like this, i have so many data. I can't do formate cell number check
1234.10. each time. Infact, formate cell number check 1234.10, this process
also not any result.

i want to do it with a click or two.

Someone pls suggest

Hi Kevin:

I think Harshad's data may contain some non-visible characters like ascii 160.

If you format a cell as Text and enter 123.4567, the formula will work and
display 123.46 and not an error.
--
Gary''s Student - gsnu200805


"Kevin B" wrote:

I believe your values are coming in as text and not as numbers. Have you
tried using the DATA/GET EXTERNAL DATA/NEW WEB QUERY to get your data from
the website in question instead of using the copy command?
--
Kevin Backmann


"Harshad" wrote:

Dear Gary's Student,
Thank you for quick reply.
i used your formula but the result is #VALUE!

Your suggesion is 100% OK for manually entered values.

if want to experience, just go to http://solvdb.ncms.org/solvdb.htm
Sleclect Option "Select By Property Range",
Enter 30 and 40 in "Boiling Point" blank cell
Hit "Display Chemical Selection"
You will get data, pls copy and paste it in excel sheet.
Now, remeining task u know very well.

Hoping your answer soon.

Thank you



"Gary''s Student" wrote:

Say we have copied values and pasted them into column A, from A1 thru A100

In B1 enter:

=ROUND(A1,2) and copy down

You can either use col B or copy col B and paste/special values back onto
column A
--
Gary''s Student - gsnu200805


"Harshad" wrote:

I copy data from one website and paste it to excel sheet. The value which is
copied is like 180.12098400983000, 20.000002300000, 79.120000,
130.25999999999999
like this, i have so many data. I can't do formate cell number check
1234.10. each time. Infact, formate cell number check 1234.10, this process
also not any result.

i want to do it with a click or two.

Someone pls suggest

I had not thought of that, Mr. Student.

Thanks for the knowledge share, I'll remember this one.

--
Kevin Backmann


"Gary''s Student" wrote:

Hi Kevin:

I think Harshad's data may contain some non-visible characters like ascii 160.

If you format a cell as Text and enter 123.4567, the formula will work and
display 123.46 and not an error.
--
Gary''s Student - gsnu200805


"Kevin B" wrote:

I believe your values are coming in as text and not as numbers. Have you
tried using the DATA/GET EXTERNAL DATA/NEW WEB QUERY to get your data from
the website in question instead of using the copy command?
--
Kevin Backmann


"Harshad" wrote:

Dear Gary's Student,
Thank you for quick reply.
i used your formula but the result is #VALUE!

Your suggesion is 100% OK for manually entered values.

if want to experience, just go to http://solvdb.ncms.org/solvdb.htm
Sleclect Option "Select By Property Range",
Enter 30 and 40 in "Boiling Point" blank cell
Hit "Display Chemical Selection"
You will get data, pls copy and paste it in excel sheet.
Now, remeining task u know very well.

Hoping your answer soon.

Thank you



"Gary''s Student" wrote:

Say we have copied values and pasted them into column A, from A1 thru A100

In B1 enter:

=ROUND(A1,2) and copy down

You can either use col B or copy col B and paste/special values back onto
column A
--
Gary''s Student - gsnu200805


"Harshad" wrote:

I copy data from one website and paste it to excel sheet. The value which is
copied is like 180.12098400983000, 20.000002300000, 79.120000,
130.25999999999999
like this, i have so many data. I can't do formate cell number check
1234.10. each time. Infact, formate cell number check 1234.10, this process
also not any result.

i want to do it with a click or two.

Someone pls suggest

Hi dear Muddan Madhu,

You are great, you solved my problem.
Thank you very much.

Please cary on such God gifted intellectual work.

With regards,
Harshad

"muddan madhu" wrote:

Try this

=ROUND(TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32))),2)


On Sep 23, 5:26 am, Harshad wrote:
I copy data from one website and paste it to excel sheet. The value which is
copied is like 180.12098400983000, 20.000002300000, 79.120000,
130.25999999999999
like this, i have so many data. I can't do formate cell number check
1234.10. each time. Infact, formate cell number check 1234.10, this process
also not any result.

i want to do it with a click or two.

Someone pls suggest