Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# I need help formatting numbers in Excel

Posted on 2013-06-12
Medium Priority
247 Views
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
Question by:mainrotor

LVL 13

Assisted Solution

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

``````=F15/100
``````

2. enter this

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

LVL 23

Accepted Solution

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

Question has a verified solution.

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

This is an article on how to answer questions, earn points and become an expert.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
###### Suggested Courses
Course of the Month11 days, 16 hours left to enroll