• 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.

About Formula Substitute

R

Raghava@rock

Member
Hi there,

i have text 8,222,90 which ideally should be 8,222.90. Inorder to do that i have used substitue formula but the same is taking the count from LEFT Instead of right.

Please help.:)
 
Hi, Raghava@rock!

Hopefully it does if from the left instead of from the right, otherwise it wouldn't be working as the manuals, the documentation and the help both built-in and online say.

If that text is in A1, try this:
=SUSTITUIR(A1;",";".";LARGO(A1)-LARGO(SUSTITUIR(A1;",";""))) -----> in english: =SUBSTITUTE(A1,",",".",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.
The same from the VBA editor, you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the same type of help.

Regards!
 
Hi Raghava ,

To add to what has already been posted , if your text strings will always have 2 digits after the last comma , you can also use :

=REPLACE(A1,LEN(A1)-2,1,".")

If you want the final output to be numeric , just add 0 to the above formula , as in :

=REPLACE(A1,LEN(A1)-2,1,".") + 0

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