Handing strings and copy paste variables-CodePudding

I have a code where I have an inputbox where I usually copy in some numbers. I need vba to handle these and pass them to the cell as european format. The format that comes in is US and comes as:
##,### ## ##

I use the following code to transform it to the format I want:

dim earned as variant

earned = Replace(Me.txtEarnedG.Value, ".", "")
earned = Replace(earned, ",", "")
earned = Replace(earned, " ", "")
earned = Left(earned, Len(earned) - 4) & "," & Right(earned, 4)

amount_earned = CDbl(earned)

This works perfectly and would transform
35,545 45 55 (US format with spaces, without a ".") into 35545,4555 (EU format)

My issue comes when I try to transform a number that doesn't 2 digits in between one of the spaces like,

35,545 4 13 becomes 3554,5413 instead of 35545,0413
35,545 12 4 becomes 3554,5124 instead of 35545,1204

I was thinking of using Instr and right in some way. But I can't figure on how to introduce it in easily. (The number should always finish with 4 decimal places, but the rest may vary and can be bigger or smaller than thousands)

CodePudding user response：

I think you could use the Split function as @Porcupine suggests and combine with Format to get the number format you wanted.

A simple example using your numbers below

Public Sub test()
    Const TEST_NUM1 = "35,545 4 13"
    Const TEST_NUM2 = "35,545 12 4"
    Const TEST_NUM3 = "35,545 45 55"
    
    FormatAsEU TEST_NUM1
    FormatAsEU TEST_NUM2
    FormatAsEU TEST_NUM3

End Sub

Public Function FormatAsEU(strNumber As String) As String    
    Dim varInput    As Variant
    
    varInput = Split(strNumber, " ")
    FormatAsEU = Format(varInput(0), "#.#") & Format(varInput(1), "00") & Format(varInput(2), "00")
    Debug.Print FormatAsEU 

End Function

Debug Results:

> 35545.0413
> 35545.1204
> 35545.4555