• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

add decimal point

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
pma111
Asked:
pma111
  • 9
  • 4
  • 4
  • +3
5 Solutions
 
Naresh PatelTraderCommented:
Try This
=LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2)

Open in new window

0
 
Naresh PatelTraderCommented:
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
 
ChrisCommented:
Assuming your number is in cell A1:

=LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance AnalystCommented:
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
 
Naresh PatelTraderCommented:
ditto  !!!!
0
 
ChrisCommented:
Ignore my comment. itjockey has already posted exactly the same formula.
0
 
pma111Author Commented:
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
 
Naresh PatelTraderCommented:
Nope .....where is your data located i.e column?
0
 
pma111Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
pma111Author Commented:
Rob H - I even get the same formula error when running your query!
0
 
Rob HensonFinance AnalystCommented:
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
 
pma111Author Commented:
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
 
Rob HensonFinance AnalystCommented:
Which nationality version of Excel are you using? Some versions require semi-colon ";" separator in the formula rather than comma ",".

Thanks
Rob H
0
 
pma111Author Commented:
its just british version of excel.
0
 
pma111Author Commented:
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
 
pma111Author Commented:
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
 
pma111Author Commented:
normal formulas i.e. =a1/2 work, it just seems to be text based formulas like len, right, left etc.
0
 
pma111Author Commented:
nor can I even do a search and replace, I must have pressed some shortcut key combo and its screwed things up.
0
 
Paul SauvéRetiredCommented:
can you post your original xls file with only the column that you want to change???
0
 
Danny ChildIT ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 9
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now