• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inserting decimal point 3 characters into a text field

J

jswinger

New Member
I have a list of text formatted cells and I need to insert a decimal point 3 characters into each field.

Examples:

31389 = 313.89

00845 = 008.45

311 = 311.0 or it can stay 311

2989 = 298.9


I can't figure out how to insert the decimal when the text field isn't a consistent length.


Thanks so much!


J
 
Hi ,


Try this :


=LEFT(A1,3)&"."&IF(LEN(A1)<=3,REPT("0",4-LEN(A1)),RIGHT(A1,LEN(A1)-3))


where A1 contains the text.


Narayan
 
Hello,

Here is one more option...

=TEXT(A1&REPT(0,ABS(LEN(A1)-5)), "000.00")


Assumption made is that the max length of the numbers is 5. If it is larger, change the "5" as appropriate.


Cheers,

Sajan.
 
@kosmonatuas

Usually we don't worry too much about multiple posts with same or similar answers, as it's always good to have more help than no help. If you ever do really need a post deleted, then either Chandoo, Hui, SirJB7, or myself can delete a post.
 
Hi jswinger,

You are welcome. Please feel free to come back any time.


I like Narayan's solution (as usual!) since it is more general purpose. But the second formula, hopefully, gives you another tool for your "bag of tools".


Cheers,

Sajan.
 
You must log in or register to reply here.
Back
Top