Conversion

[barrowhill]

Just been contacted by my son who living and working in Romania as an
accountant.
Their accounting software is Romanian and cannot export to Excel as used in
my son's firm.

They have provided him with a file in html which via a conversion program
has been copied into excel.

My son says that there is about 1500 lines and he would like to auto sum one
(or more) columns. The issue is how the Romanians represent there number
system i.e. "one thousand pounds and 99 pence" in UK representation iwould
be £1,000.99. In Romania this would (and is in the spreadsheet) be shown as
£1.000,99 i.e the comma's and dots are reversed.

How would one reverse this representation to enable an auto sum to be
performed.

Appreciate help in providing son with a resolution to his problem

 

[Sheeloo]

Assuming the values as text in the imported Excel...

First search and replace . by nothing
Then search and replace , by .
and you should be able to go.

If not then send the file to me
append @hotmail.com to to_sheeloo to get my id.

 

[Gord Dibben]

Might be just as easy to go to Tools>Options>International and change from
system separators to custom.


Gord Dibben MS Excel MVP

 

[barrowhill]

Sheeloo,

Thanks for reply. Solution appears simple.

I'm running Excel 2003. To test I've put a column of numbers from 1.000,99
thru 1.000,120 in and ensured that the column format is "text"

When I replace as you suggest - Edit>Replace>Find what [.]> Replace with []

Repalce All (or just replace one a a time), the first entry removes .(dot)

but subsequent entries remove "." (dot) AND "," (comma) ??????

Can you test an confirm? any explanation???.

 

[Sheeloo]

I have Excel 2007 and my tests ran successfully.

You may try Gordon's solution first. That is a better solution.

Or try replacing . with something like QQ, then replace , with . and finally
QQ with nothing. The trick is NOT to let Excel treat the entries as numbers
till you have converted them all.

Sheeloo,

Thanks for reply. Solution appears simple.

I'm running Excel 2003. To test I've put a column of numbers from 1.000,99
thru 1.000,120 in and ensured that the column format is "text"

When I replace as you suggest - Edit>Replace>Find what [.]> Replace with []

Repalce All (or just replace one a a time), the first entry removes .(dot)

but subsequent entries remove "." (dot) AND "," (comma) ??????

Can you test an confirm? any explanation???.

Assuming the values as text in the imported Excel...

First search and replace . by nothing
Then search and replace , by .
and you should be able to go.

If not then send the file to me
append @hotmail.com to to_sheeloo to get my id.

 

[barrowhill]

Gord,

Of course!!!. Never thought to look. Thanks very much.

 

[barrowhill]

Sheeloo,

At first galnce, and as I responded to Gorden, the solution looked simple.
However despite trying, not working in Excel 2003. Perhaps 2007 fixed all
these anomalies. Your additional suggestion is the one that works 100% in
2003 so I'll forward that as solution, Thanks for help.

I have Excel 2007 and my tests ran successfully.

You may try Gordon's solution first. That is a better solution.

Or try replacing . with something like QQ, then replace , with . and finally
QQ with nothing. The trick is NOT to let Excel treat the entries as numbers
till you have converted them all.

Sheeloo,

Thanks for reply. Solution appears simple.

I'm running Excel 2003. To test I've put a column of numbers from 1.000,99
thru 1.000,120 in and ensured that the column format is "text"

When I replace as you suggest - Edit>Replace>Find what [.]> Replace with []

Repalce All (or just replace one a a time), the first entry removes .(dot)

but subsequent entries remove "." (dot) AND "," (comma) ??????

Can you test an confirm? any explanation???.

Assuming the values as text in the imported Excel...

First search and replace . by nothing
Then search and replace , by .
and you should be able to go.

If not then send the file to me
append @hotmail.com to to_sheeloo to get my id.

:

Just been contacted by my son who living and working in Romania as an
accountant.
Their accounting software is Romanian and cannot export to Excel as used in
my son's firm.

They have provided him with a file in html which via a conversion program
has been copied into excel.

My son says that there is about 1500 lines and he would like to auto sum one
(or more) columns. The issue is how the Romanians represent there number
system i.e. "one thousand pounds and 99 pence" in UK representation iwould
be £1,000.99. In Romania this would (and is in the spreadsheet) be shown as
£1.000,99 i.e the comma's and dots are reversed.

How would one reverse this representation to enable an auto sum to be
performed.

Appreciate help in providing son with a resolution to his problem

 

[Sheeloo]

Thanks for the feedback. Glad you could solve your problem.

Unfortunately I have misplaced my CD of Office 2003 so I, currently, do not
have access to Excel 2003...

Sheeloo,

At first galnce, and as I responded to Gorden, the solution looked simple.
However despite trying, not working in Excel 2003. Perhaps 2007 fixed all
these anomalies. Your additional suggestion is the one that works 100% in
2003 so I'll forward that as solution, Thanks for help.

I have Excel 2007 and my tests ran successfully.

You may try Gordon's solution first. That is a better solution.

Or try replacing . with something like QQ, then replace , with . and finally
QQ with nothing. The trick is NOT to let Excel treat the entries as numbers
till you have converted them all.

Sheeloo,

Thanks for reply. Solution appears simple.

I'm running Excel 2003. To test I've put a column of numbers from 1.000,99
thru 1.000,120 in and ensured that the column format is "text"

When I replace as you suggest - Edit>Replace>Find what [.]> Replace with []
Repalce All (or just replace one a a time), the first entry removes .(dot)
but subsequent entries remove "." (dot) AND "," (comma) ??????

Can you test an confirm? any explanation???.



:

Assuming the values as text in the imported Excel...

First search and replace . by nothing
Then search and replace , by .
and you should be able to go.

If not then send the file to me
append @hotmail.com to to_sheeloo to get my id.

:

Just been contacted by my son who living and working in Romania as an
accountant.
Their accounting software is Romanian and cannot export to Excel as used in
my son's firm.

They have provided him with a file in html which via a conversion program
has been copied into excel.

My son says that there is about 1500 lines and he would like to auto sum one
(or more) columns. The issue is how the Romanians represent there number
system i.e. "one thousand pounds and 99 pence" in UK representation iwould
be £1,000.99. In Romania this would (and is in the spreadsheet) be shown as
£1.000,99 i.e the comma's and dots are reversed.

How would one reverse this representation to enable an auto sum to be
performed.

Appreciate help in providing son with a resolution to his problem