How to get currency from formatted cell into new cell 1

[franksirvent]

Hello
I have a number of cells with numbers which are formated as currency $

The column is M (for example)

I need to copy the value from M5 (eg $10.00) to cell N5 and the currency from M5 to cell O5

So basically from $10.00 i need it to become two cells, $, and 10.00

There are different currencies on each cell so I cannot simply put $ on cell O5.

Could any one help me with this ???

thanks in advance

 

[HarleyQuinn]

What currencies will you be dealing with?

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Actually ignore that last post, it is irrelevant. There might be an easier way to do this but you could modify this code to suit your needs:

Private Sub CommandButton1_Click()
Dim strCurrency As String
Dim strAmount As String
Dim i As Integer
Dim strChar As String

Range("A1").Select

For i = 1 To Len(Selection.Text)

    strChar = Mid$(Selection.Text, i, 1)
    
    Select Case IsNumeric(strChar)
    
        Case True
            strAmount = strAmount & strChar
        Case False
            If strChar = "." Then
            strAmount = strAmount & strChar
            Else
            strCurrency = strCurrency & strChar
            End If
            
    End Select
    
Next i

Range("F1").Value = strCurrency
Range("F2").Value = strAmount

End Sub

The above code loops throught the text in the cell and if it is either a number or a decimal point adds it to the amount (strAmount), if it is neither numeric or a decimal point it adds it to the Currency (strCurrency). You will need to modify it to look at the cells specified in your original post but it should do the trick for you. As I say there might be an easier way to do this but I can't think of one at the minute.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Please note, this code will not work correctly for most of the former Soviet states and some currency notations for Swiss francs. This is because their currency notations include full stops (e.g. Sfr. for Swiss francs).

Harleyquinn

---------------------------------
For tsunami relief donations

 

[franksirvent]

THANKS Harleyquinn !!!!!!!!!


IT WORKS PERFECTLY !!!!!
IS EXACTLY WHAT I WAS LOOKING FOR !!!!

 

[HarleyQuinn]

Glad I could help

Harleyquinn

---------------------------------
For tsunami relief donations