Check and change SAP Excel Download monetary formats i.e. swap . for , and , to . if incor

[robertguy]

Hi,

My problem is every week I import data from as SAP download (in an Excel.xls format) into a template Excel file, however, on occasions the SAP download for some unknown reason will change , (commas) to. (periods) in columns that contain monetary values e.g. 1.234,56

So under normal circumstances the value from SAP would be in this format 1,234.56 but occasionally the format would be 1.234,56

Can anyone please advise me of the VBA code to check my data in say in column ‘A’ (please attached Example file) to check if it’s in the correct format e.g. 1,234.56 and if not change the format i.e. change the , (commas) to. (periods) and the . (periods) to (commas)

Any assistance in this matter would be greatly appreciated

Many thanks in advance

Regards

Rob

[Roel Jongman]

I do not see any VBA code in your example file.
How do you "import" the data into excel? does SAP provide an addin that allows you to retrieve the data from or do you simply download the information in sap yourself (or a collegue) and after that open the downloaded excel file in excel?
also your profile does not state any version of Excel. It is helpfull to know which version you are using for this job, so other options might be available too to tackle this problem..

It is important to know as there is little point in just building random VBA code when it might interfere with other code in use eighter self build or available thru an addin.

[robertguy]

Roel Jongman,

sorry I should have said the import is manual so I will need macro/VBA to run after I have imported the data manually

Regards


Rob

[Roel Jongman]

would a manual solution suffice then too? there is a few easy search and replace tricks that will fix your problem too..

do 2 search and replace actions. when the numbers show as 1.234,56

Delete thousand separators.
1. Select the problem column
2. put . in seachbox and leave replace field empty
3. choose replace all

replace decimal separators
1. select problem column (with removed thosandseparators)
2. put , in the search box and . in the replacebox
3. choose replace all.

- make sure you do this only on a selected column. If you search and replace on the whole sheet any descriptions containing point of comma might be affected by the search and replaced too


Also have a look at the options of SAP download and see if you can make downloads without thousand separators. they are not needed and the cause of the problem
Excel will identify numbers with only the wrong decimal separators but no thousandseparators as numbers.
But when thousand separators are in the numbers excel does not resolve it herself.

the thousand separator can be shown in excel easily by re-formatting the column

[robertguy]

Roel Jongman,

I did try this and it works, that said, if the data is in the correct format in the first place it will put it in the format I'm trying to amend, and hence the need for some VBA script to check the format in the first instance and then run some code to amend if required.


Regards

Rob