Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

add decimal point

Posted on 2014-02-17
21
Medium Priority
?
158 Views
Last Modified: 2014-02-20
I have a column in an excel spreadsheet (currently) formatted in general which is essentially a number i.e. 456789, in the column next to it I need a formula whereby it adds a "." 3 characters in from the right, i.e. 456789 needs to become 4567.89

any ideas how this could be done?
0
Comment
Question by:pma111
[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
  • 9
  • 4
  • 4
  • +3
21 Comments
 
LVL 8

Accepted Solution

by:
Naresh Patel earned 400 total points
ID: 39864561
Try This
=LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2)

Open in new window

0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39864568
This is as per your result but you said
it adds a "." 3 characters in from the right

then there will be different formula. pls clarify.

Thanks
0
 
LVL 12

Assisted Solution

by:Chris
Chris earned 400 total points
ID: 39864571
Assuming your number is in cell A1:

=LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2)
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 800 total points
ID: 39864574
If the number is literally a number, just dividinbg by 100 will add the decimal point. This can then be converted to text if so required:

=A2/100

or

=TEXT(A2/100,"0000.00")

Thanks
Rob H
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39864575
ditto  !!!!
0
 
LVL 12

Expert Comment

by:Chris
ID: 39864577
Ignore my comment. itjockey has already posted exactly the same formula.
0
 
LVL 3

Author Comment

by:pma111
ID: 39864581
for both I get a formula error, I changed the cells accordinly. Could this be to do with how the current column is formatted, and the formula column is formatted? both are currently formatted "general".
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39864593
Nope .....where is your data located i.e column?
0
 
LVL 3

Author Comment

by:pma111
ID: 39864605
AA2:200, and in AB2I put the formula and changed values accordinly and get this error:

http://www.treeplan.com/images/formula-typed-error.jpg

file is formatted csv, excel 2010.
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 800 total points
ID: 39864612
Is the contents of the cell just the 6 digits or does it have leading/trailing spaces?

If extract from another system (assumed link to other question about 7/8 characters) then it may be a non-visible character at the start of the cell. This may not be a normal "space" character which can be renoved using trim, you may have to use other forms of cleansing.

Use the following to determine which character is at the start of the cell:

=CODE(LEFT(A2,1))

A normal space will give the number 32.

Thanks
Rob H
0
 
LVL 3

Author Comment

by:pma111
ID: 39864624
Rob H - I even get the same formula error when running your query!
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39864625
That error message normally suggests you have missed something from the formula and Excel is unable to determine and correct the error automatically, eg missing brackets where it is not obvious where the missing brackets should be or inclusion of text within double quotes that are not closed out.

Thanks
Rob H
0
 
LVL 3

Author Comment

by:pma111
ID: 39864629
this is crazy, I created a new spreadsheet, copied and pasted some sample data and still I get the same error. to any formula, what the hell?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39864630
Which nationality version of Excel are you using? Some versions require semi-colon ";" separator in the formula rather than comma ",".

Thanks
Rob H
0
 
LVL 3

Author Comment

by:pma111
ID: 39864631
its just british version of excel.
0
 
LVL 3

Author Comment

by:pma111
ID: 39864640
this is crazy, if I open excel on another machine, run the same query it works fine. So its like some issue with the software.
0
 
LVL 3

Author Comment

by:pma111
ID: 39864696
anyone got a theory why that formula would work on one installation of excel and not another. could it be a global setting in excel affecting this?
0
 
LVL 3

Author Comment

by:pma111
ID: 39864707
normal formulas i.e. =a1/2 work, it just seems to be text based formulas like len, right, left etc.
0
 
LVL 3

Author Comment

by:pma111
ID: 39864812
nor can I even do a search and replace, I must have pressed some shortcut key combo and its screwed things up.
0
 
LVL 33

Expert Comment

by:Paul Sauvé
ID: 39865348
can you post your original xls file with only the column that you want to change???
0
 
LVL 23

Assisted Solution

by:Danny Child
Danny Child earned 400 total points
ID: 39866257
if you don't care about changing the underlying number, just how it "appears", then apply a Custom format to the cells of
0000"."00

if you DO want to change the numbers permanently, find a spare cell, put 100 in it, copy it,
select your number range, choose Paste Special - and then choose the Divide option.
Job Done.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

610 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