Solved

I need help formatting numbers in Excel

Posted on 2013-06-12
2
237 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

691 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