UPDATE: DOH I can divide by 1000.

I need to convert a column of money data into a format that is recognized by QuickBooks Online so I can upload bank account transactions. I am in Tunisia where the bank gives us the data in a bad format that needs to be converted.

The money data looks like this.
Amount
-565,000
-400,645
-5,000

This needs to look like
Amount
-565.000
-400.645
-5.000

I thought I could easily change the "," to an "." but the "," is not actually in the field as as excel thinks its the thousand character. Also thought I could use LEFT and RIGHT but the LEFT numbers are always different. The RIGHT numbers will always be 3 though as Tunisian Dinar is thousandth of a DINAR.

Hope I am making sense and that someone can help.
thanks in advance.

I need to convert a column of money data into a format that is
recognized by QuickBooks Online so I can upload bank account
transactions. I am in Tunisia where the bank gives us the data in a
bad format that needs to be converted.

The money data looks like this.
Amount
-565,000
-400,645
-5,000

This needs to look like
Amount
-565.000
-400.645
-5.000

I thought I could easily change the "," to an "." but the "," is not
actually in the field as as excel thinks its the thousand character.
Also thought I could use LEFT and RIGHT but the LEFT numbers are
always different. The RIGHT numbers will always be 3 though as
Tunisian Dinar is thousandth of a DINAR.

Hope I am making sense and that someone can help.
thanks in advance.

You can do this with VBA. Change the Number format of the column to
text, select the cells with amounts and loop through the selection...


Sub ReformatBankAmounts()
Dim c As Range
For Each c In Selection
c = Format(c / 1000, "000.000")
Next 'c
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Enter 1000 in a cell.

Copy that cell

Select you range of money data and paste specialdivideokesc

Delete the 1000 cell.

Gord



On Tue, 26 Aug 2014 13:45:58 +0100, IpswichBlues
wrote:


I need to convert a column of money data into a format that is
recognized by QuickBooks Online so I can upload bank account
transactions. I am in Tunisia where the bank gives us the data in a bad
format that needs to be converted.

The money data looks like this.
Amount
-565,000
-400,645
-5,000

This needs to look like
Amount
-565.000
-400.645
-5.000

I thought I could easily change the "," to an "." but the "," is not
actually in the field as as excel thinks its the thousand character.
Also thought I could use LEFT and RIGHT but the LEFT numbers are always
different. The RIGHT numbers will always be 3 though as Tunisian Dinar
is thousandth of a DINAR.

Hope I am making sense and that someone can help.
thanks in advance.