• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1920
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# how do i remove decimal points and also add zeros to excel

how do i remove decimal points and also add zeros to excel values; for example: make 1.3 1300
Hello again and thank you for the request.

You can setup a formula to do this:

If 1.3 was in the A1 cell, you could use the following formula:

=A1*1000

And then you could copy that down the colum.
Jason Jones, Computer Technician
Category: Microsoft Office
Satisfied Customers: 15224
Experience: Over 10 years work experience.
Customer: replied 4 years ago.

however, what if some of the values contain a letter preceding the number:

e.g. V1.3

want to make V130

Please, give me a few more examples what what you need help with.

Thank you.
Customer: replied 4 years ago.

v1.30

E4.56

V13.56

V3.2

I NEED TO MAKE INTO

v130

E456

V1356

V320

When does the value have a trailing 0 added to it, as your last example did? (V320)
Customer: replied 4 years ago.

THE NUMBER HAS TO HAVE MINIMUM 3 NUMBERS MAX 5

Customer: replied 4 years ago.

After you do the replace all, which will remove the periods, you can use the following formula:
=LEFT(A1,1)&IF(LEN(A1)=3,RIGHT(A1,LEN(A1)-1)*10,IF(LEN(A1)>3,RIGHT(A1,LEN(A1)-1)))

Jason Jones, Computer Technician
Category: Microsoft Office
Satisfied Customers: 15224
Experience: Over 10 years work experience.
Customer: replied 4 years ago.

the problem is i cannot do the replace all in the values where there are letters. what is 'replace all?' did we discuss this?

also the example you sent has two identical columns....

I am sorry for the misunderstanding.

- In order to replace all, click the column header (A, B, C, etc), press CTRL H
- Once that comes up, put a . in the find what field and click the Replace All button.

Once that is complete, periods will be gone and the formula will work.

The columns were not identical.

Look at the last row.
Jason Jones, Computer Technician
Category: Microsoft Office
Satisfied Customers: 15224
Experience: Over 10 years work experience.
Customer: replied 4 years ago.

this is excellent and worked almost perfectly. the only problem, and it was probably because i did not tell you all the information, is that i get errors when the values are only 1 or 2 numbers.

could you explain the concept of this code?

finally, would it be possible to add one zero to all numbers that are only two digits and add 2 zeros to all numbers that are one digit. remember how i said the minimum should be three numbers?

thanks.

Here is the corrected formula:

=LEFT(A1,1)&IF(LEN(A1)=3,RIGHT(A1,LEN(A1)-1)*10,IF(LEN(A1)>3,RIGHT(A1,LEN(A1)-1),IF(LEN(A1)=2,RIGHT(A1,LEN(A1)-1)*100)))

Customer: replied 4 years ago.

this is not working for me, i think there is something wrong

What is it doing?
Customer: replied 4 years ago.

it is doing it for the row above it.

What does that mean?

That means that you copied the formula into the wrong cell.

It sounds like you copied it into the 2nd row first and not into to the 1st row.

After you paste it into the 1st row, copy that cell and paste it into the column.
Customer: replied 4 years ago.

i am having a hell of a time copying the formula from your file to my file. when i copy the formula you provided here, i am getting errors in cells with only one digit.

May I take a look at your workbook?

Please, be forewarned that the site is not secure:
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 4 years ago.

Ok.

It appears that some of the values do not have a letter in front of them. I was not aware that this would be the case.

I will have thsi fixed very soon and posted.
I cannot get this working.

I have run out of ideas.

For this reason, I am opting out of the question and allowing another expert the chance to help.

I wish you luck,
- Jason

My Name isXXXXX will be helping you today. If I provide good service, you may ask for The-PC-Guy in your question title for your future needs.

this is much simpler that the previous expert made it.

Just use the following formula

Assuming the first number is XXXXX A2

=SUBSTITUTE(A2,".","")

Then just copy down the column

feel free to ask for me again in the future if I can be of any more assistance,

If you want the right answer the first time, please contact me directly for all of your future computer questions, to get the fastest and most accurate service possible.

PLEASE DON'T FORGET TO RATE ME GOOD OR EXCELENT SERVICE, 4 OR 5 STARS OR SO I WILL BE PAID FOR MY TIME.

Customer: replied 4 years ago.

it is not just about decimal points. you need to read the whole thread. it is more complicated than removing a decimal point.

v1.30

E4.56

V13.56

V3.2

I NEED TO MAKE INTO

v130

E456

V1356

V320

i see you said this, what doesn't make sense is why you want to turn V3.2 into V320 and why now E4.56 into E4560?

What is the criteria for adding a zero. Is it that all values need to have a minimum of a Letter and 3 numbers, and if it has less you want to add the zero to keep it the same?

Please give me your criteria, and I can make the modifications. This is all easily doable.

Customer: replied 4 years ago.

sure.

there needs to be a minimum of 3 numbers. Thus if a number is XXXXX digit, two zeroes needs to be added. If a number is XXXXX digits, one zero needs to be added. Otherwise, if number is XXXXX 4, or 5 digits, nothing needs to be added just a removal of decimal point...

ok thats easy

Try this, it can be tweaked, if we missed a specific occurrence

=IF((LEN(A2)-1)<4,CONCATENATE(SUBSTITUTE(A2,".",""),REPT("0",4 - (LEN(A2)-1))),SUBSTITUTE(A2,".",""))
Customer: replied 4 years ago.

won't work.....nothing happens, it does not even run. i have excel windows 7.

you have to copy and paste the formula into a cell it will have to be adjusted down a column based on the reference column.

Would it be easier for you to send me the workbook and have me add the formula for you?
Customer: replied 4 years ago.

the workbook is in the thread..........wikisend......here it is again:

sorry, missed it.

So you want everything in column B to = Column A with 3 - 5 digits?
Customer: replied 4 years ago.

sorry, missed it.

yes I removed them

So you want everything in column B to = Column A with 3 - 5 digits?

yes including the ones with letters infron of them at the bottom of column A.

ok, lemme know if this works for you. Don't forget to rate if it does.

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1920
Experience: 20 years experience providing remote computer support
Customer: replied 4 years ago.

I certainly won't forget to rate.

however, could you tell me what the file is called? When I open I see a bunch of .xlm files. when it opens it doesn't even make sense....

I just downloaded and modified your file, If it had any xlm files then they would have been in your original. I did not see any VBA in this file.

I renamed it to ja.xlsx, with the working formula. And uploaded it to wikisend as a new file. So you wouldn't be confused with the original
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1920
Experience: 20 years experience providing remote computer support