formula for a decimal point in excel

[lake281]

My example: here is some numbers: 00230, 12365, 1234
I need to place a decimal point after 3rd number from the left.. so I would get the following results: 002.30, 123.65 and 123.4
How do I do that? please

 

[SimonDavis]

Would it b really stupid to suggest divide it by 100?

 

[lake281]

hm, let's look at the last number 1234, if you divide the number by 100 = result will be 12.34 my problem is that the decimal point needs to be after 3rd number from the left

 

[SkipVought]

Hi,

1234 - 123.4 does not fit your OTHER pattern.

???

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[lake281]

Skip, yes... that is my dilema ;o)) numbers are different

 

[SimonDavis]

Well, looks like I can't help you then.

 

[SkipVought]

What's the LOGIC that makes that one different?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Molby]

Try something like:
=LEFT(A1,3) & "." & RIGHT(A1,(LEN(A1)-3))

 

[Zathras]

Assuming your "numbers" are really text (as indicated by the leading zero on the first and only 4 characters on the last) then this formula should give you what you want:

  =LEFT(A1,3)&"."&MID(A1,4,999)

To see whether your "numbers" are numbers or text, try the following formula to see what you get:

  =TYPE(A1)

If it shows a 2, then it is really text. If it is really a number, then it would show a 1.

 

[AnotherHiggins]

=left(a1,3)&"."&right(a1,len(a1)-3)

John

Oh pointy bird,
Oh pointy pointy,
Anoint my head,
Anointy nointy.
Steve Martin in The Man With 2 Brains and L.A. Story
To get the best answers fast, please read faq181-2886

 

[lake281]

Thank YOU!
o))))

 

[SimonDavis]

OK, wait a minute. I was reading left as right (doh)

Are these numbers actually numbers, or are they text? the leading zeros are a little strange . . .

Solution - a little complex, but you'll get there.

- assuming those numbers are in column a;

col b: =len(a1)

that will tell you how long the number is. So you'll get 5 for the first one.

col c: =left(a1,3)

Will give you the first 3 numbers.

col d: =right(a1,(b1-3))

Will return the remaining numbers.

col e: =concatenate(c1,".",d1)

That will work.

Watch those leading zeros though.

 

[SimonDavis]

Oh well

 

[SkipVought]

Here's the problem.

If 00230 <> 230 then you are not dealing with NUMBERS. rather, you are dealing with STRINGS of numeric digits. BIG DIFFERENCE.

If your NUMBER, 230 is FORMATTED with leading ZEROS, it's STILL 230 and using =LEFT(A1,3) returns 230.

The OTHER thing is that =LEFT(A1,3) & "." & RIGHT(A1,(LEN(A1)-3)) returns a STRING that will NOT calculate arithmatically under some circumstances. Excel has some smarts to do the STRING to NUMBER conversion, but it can cause problems.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

For instance, once the Left&.&Right formula resutls are displayed, the SUM function returns ZERO, while ADDING each individual result returns the sum.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

so...

knowing all this, just change the formula by converting the result to a number...
[tt]
=VALUE(LEFT(A1,3)&"."&RIGHT(A1,2))
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SimonDavis]

Skip - the numbers aren't all the same length.

 

[SkipVought]

[tt]
=VALUE(LEFT(A1,3)&"."&RIGHT(A1&LEFT("0000",5-LEN(A1)),2))
[tt]
works on 1234 ==> 123.4

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}