French Number format to Indian Number format

Convert data in the format 99.999.999,88 to 99,999,999.88 i.e French system to British System

Background

We in India (and most of the world) follow number format where we use comma [,] for separator and Full stop [.] for decimal separator.

Excel will recognise the following type of data as number and let you use to perform numerical calculations like SUM, AVERAGE, MAX and so on.
eg –   8,00,660.78
eg – 99,00,780.34
eg –         3489.8980

But if you export data from a ERP which is setup in one of the following country,

• Canadian
• Danish
• Finnish
• French
• German
• Italian
• Norwegian
• Spanish
• Swedish

eg –   8.00.660,78
eg – 99.00.80,34
eg –         3489,8980
Excel will consider them as Text and will not let you use them for performing numerical calculations like SUM, AVERAGE, MAX and so on.

Use

The AdarshFrenchToIndian User Defined function will help you convert French number format to Indian number format.

Function AdarshFrenchToIndian(FrenchNumber As String) As Double

''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Created by    : Adarsh Madecha
' Created on    : 08 January 2016
' Version       : 1.0
' Copyright     : All rights reserved.
' Website       : madrecha.com
' Blog          : adarshmadrecha.blogspot.com
' Twitter       : @adarshMadrecha
' If you want to share this code, ask that person to visit - adarshmadrecha.blogspot.com

'-----------Purpose of this fucntion------------------
' Using     : Fuction accepts only one parameter. You need to provide Number (which is in the french format) in the fucntion.
'            '   eg >    8,00,660.78   from >  8.00.660,78
'            '   eg >   99,80,780.34   from > 99.80.780,34
'            '   eg >        3489.898  from >      3489,8980
''''''''''''''''''''''''''''''''''''''''''''''''''''''

'define tempText
Dim tempText As String

' Clean and Trim
tempText = Application.WorksheetFunction.Clean(FrenchNumber)
tempText = Application.WorksheetFunction.Trim(tempText)

' Delete .  and Space
tempText = Replace(tempText, " ", "")
tempText = Replace(tempText, ".", "")

'Replace , with .
tempText = Replace(tempText, ",", ".")

'retrun value
AdarshFrenchToIndian = CDbl(tempText)

End Function

 

This blog is owned by CA Adarsh Madrecha. All rights reserved.