?
Solved

I need help formatting numbers in Excel

Posted on 2013-06-12
2
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Assisted Solution

by:Shanan212
Shanan212 earned 1000 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 1000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 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