Conversion
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 |
Conversion
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" wrote: 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 |
Conversion
Might be just as easy to go to ToolsOptionsInternational and change from
system separators to custom. Gord Dibben MS Excel MVP On Sun, 18 Jan 2009 10:46:00 -0800, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote: 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" wrote: 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 |
Conversion
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 - EditReplaceFind 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" wrote: 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" wrote: 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 |
Conversion
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. "barrowhill" wrote: 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 - EditReplaceFind 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" wrote: 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" wrote: 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 |
Conversion
Gord,
Of course!!!. Never thought to look. Thanks very much. "Gord Dibben" wrote: Might be just as easy to go to ToolsOptionsInternational and change from system separators to custom. Gord Dibben MS Excel MVP On Sun, 18 Jan 2009 10:46:00 -0800, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote: 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" wrote: 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 |
Conversion
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. "Sheeloo" wrote: 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. "barrowhill" wrote: 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 - EditReplaceFind 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" wrote: 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" wrote: 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 |
Conversion
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... "barrowhill" wrote: 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. "Sheeloo" wrote: 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. "barrowhill" wrote: 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 - EditReplaceFind 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" wrote: 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" wrote: 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 |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com