Solved

I need help formatting numbers in Excel

Posted on 2013-06-12
2
233 Views
Last Modified: 2013-06-18
Hi Experts,
I have a couple of ExCEL questions.  
1) If I have a column of numbers without a decimal point and would like to make the two right-most characters the decimal values, how would I do this?

for example:
15895  should become  158.95
75642  should become  756.42


2) If I have a column with 10 characters and some of those characters are negative numbers with leading 0's, how can I remove the leading 0's?

for example:
000-2548  should become -2548
00-14587  should become -14587


Thank you very much in advance,
mrotor
0
Comment
Question by:mainrotor
2 Comments
 
LVL 13

Assisted Solution

by:Shanan212
Shanan212 earned 250 total points
ID: 39241620
1. just divide by 100

=F15/100

Open in new window


2. enter this

=MID(F15,FIND("-",F15),LEN(F15))

Open in new window

0
 
LVL 23

Accepted Solution

by:
NBVC earned 250 total points
ID: 39241659
Without formulas:

1.  Enter 100 in an empty cell and copy that cell, then select the range to change, right-click and select Paste Special, then select Divide and click Ok.

2. Select the range to fix, hit CTRL+H (or Edit|Replace) and enter *- (i.e. asterisk and minus) in the Find What field.  Enter just a minus (-) in the Replace with field.  Hit Replace All.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 78
Clear Filter 8 41
Data Copy 4 28
Return a string either side of a specific character in that string - Excel 6 18
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question