• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1824
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

This answer was rated:
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: 13960
Experience: Over 10 years work experience.
Jason Jones and 6 other Microsoft Office Specialists are ready to help you
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.

PLEASE EXPEDITE ANSWER FOR GOOD RATING AND POTENTIALLY A BONUS

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: 13960
Experience: Over 10 years work experience.
Jason Jones and 6 other Microsoft Office Specialists are ready to help you
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: 13960
Experience: Over 10 years work experience.
Jason Jones and 6 other Microsoft Office Specialists are ready to help you
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.

I downloaded from the link you sent, and don't see any decimal points.

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

sorry, missed it.

I downloaded from the link you sent, and don't see any decimal points.
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: 1824
Experience: 20 years experience providing remote computer support
The-PC-Guy and 6 other Microsoft Office Specialists are ready to help you
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: 1824
Experience: 20 years experience providing remote computer support
The-PC-Guy and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.

if you are a/v asap, i made a mistake and asked you to add two ending zeros. I meant add leading zeroes. that is two zeroes for a one digit, and one zero for a two digit. I have to have results this morning....

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Previous | Next >
• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
• Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland

• jstinehelfer

Satisfied Customers:

36
A+ Comptia Certified computer repair
< Last | Next >

jstinehelfer

Satisfied Customers:

36
A+ Comptia Certified computer repair

JasonJames122

Satisfied Customers:

0
I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

Jess M.

Satisfied Customers:

552
Computer Software Specialist for more than 10 years

The-PC-Guy

Satisfied Customers:

496
20 years experience providing remote computer support

James K.

Satisfied Customers:

260
Technical Director of IT Company

IT Miro

Satisfied Customers:

151
Bachelor's Degree in Information Technology, Microsoft Certified Professional