CODEWITHSUNDEEP
excelexcel-formula
eegloo
eegloo

Reputation: 1499

How to convert INR number format into European number format in Excel with Formulas?

Following is the which I am trying :

  1. Let this number 6,123,456.33 in Cell A1,

  2. Then in Cell B1 use this formula =TEXT(A1,"#,###,###.##"), will give you 6,123,456.33.

  3. Then in Cell C1 use this formula = SUBSTITUTE(B1,",",".") ,will give you 6.123.456.33

  4. Then in Cell D1 use this formula =","&RIGHT(H12,2), will give you ,33.

  5. Then again come to Cell C1 Do text to columns or other options to remove the last digits with decimals and then concatenate result with Cell D1 shows the last three digits.

  6. This tip will ends up in 6.123.456,33

But Problem is in point no. 5. How should I remove .33 from cell C1?

TRUNC is not working on C1.

Any Suggestions ?

Upvotes: 0

Views: 3339

Answers (2)

barry houdini
barry houdini

Reputation: 46371

If A1 is always a 7 digit number with 2 decimals then you could use TEXT function like this:

=TEXT(A1*100,"0\.000\.000\,00")

Upvotes: 1

Jerry
Jerry

Reputation: 71578

Seems like you don't mind having the result as text (and I can't seem to find a way to custom format it...) and as such, you can use the formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1,"#,###.00"),",","@"),".",","),"@",".")

There's a triple substitution, one to remove , for @ (a dummy character), second to change . to ,, then last from the dummy @ to ..

Upvotes: 3

Related Questions