How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1377
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

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

Resolved Question:

how do i remove decimal points and also add zeros to excel values; for example: make 1.3 1300
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Jason replied 2 years ago.
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, Computer Technician
Category: Microsoft Office
Satisfied Customers: 10846
Experience: Over 10 years work experience.
Jason and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.


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


e.g. V1.3


want to make V130

Expert:  Jason replied 2 years ago.

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

Thank you.
Customer: replied 2 years ago.

 


v1.30


E4.56


V13.56


V3.2


 


I NEED TO MAKE INTO


v130


E456


V1356


V320


 

Expert:  Jason replied 2 years ago.

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


THE NUMBER HAS TO HAVE MINIMUM 3 NUMBERS MAX 5

Customer: replied 2 years ago.

 


PLEASE EXPEDITE ANSWER FOR GOOD RATING AND POTENTIALLY A BONUS

Expert:  Jason replied 2 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)))

Here is an example workbook: http://wikisend.com/download/321898/Book1.xls
Jason, Computer Technician
Category: Microsoft Office
Satisfied Customers: 10846
Experience: Over 10 years work experience.
Jason and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 2 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....

Expert:  Jason replied 2 years ago.

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, Computer Technician
Category: Microsoft Office
Satisfied Customers: 10846
Experience: Over 10 years work experience.
Jason and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 2 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.

Expert:  Jason replied 2 years ago.

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)))

Here is the corrected workbook: http://wikisend.com/download/435804/BookFixed.xls
Customer: replied 2 years ago.


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

Expert:  Jason replied 2 years ago.

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


it is doing it for the row above it.

Expert:  Jason replied 2 years ago.

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

Expert:  Jason replied 2 years ago.

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"
Expert:  Jason replied 2 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.
Expert:  Jason replied 2 years ago.
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
Expert:  The-PC-Guy replied 2 years ago.

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,

 

http://www.justanswer.com/computer/expert-1expert-2rulethemall/
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 Laughing SO I WILL BE PAID FOR MY TIME.

Customer: replied 2 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.


 

Expert:  The-PC-Guy replied 2 years ago.

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

Expert:  The-PC-Guy replied 2 years ago.
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 2 years ago.


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

Expert:  The-PC-Guy replied 2 years ago.
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 2 years ago.


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


 


http://wikisend.com/download/437998/Dtab10_fixed_parsed.xls

Expert:  The-PC-Guy replied 2 years ago.
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 2 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.

Expert:  The-PC-Guy replied 2 years ago.
ok, lemme know if this works for you. Don't forget to rate if it does.

http://wikisend.com/download/955926/ja.xlsx
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1377
Experience: 20 years experience providing remote computer support
The-PC-Guy and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 2 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....

Expert:  The-PC-Guy replied 2 years ago.
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: 1377
Experience: 20 years experience providing remote computer support
The-PC-Guy and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 2 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
< Last | 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
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

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

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
The-PC-Guy
The-PC-Guy
Microsoft Office Technician
1377 Satisfied Customers
20 years experience providing remote computer support