Ideas behind the conversion of German numbers and sum calculation

[Anonymous]

Hi,

I have a data set whose values are in German number format. When I transfer the data from MS Excel to a collection in Blue Prism, it is converted to text. But I have to add all the values and divide by 1000. After that I have to round it. Can anyone give me a hint on how can I do this? 

(Note that: the data is dynamic, and I need to enter the sum in another excel file in German format)
Formula: -ROUND((B2+B3+B4+B5+B6)/1000;0)
The data (-1.223.375,15) in UK format (-1,223,375.15)

Name	Value
A	-1.223.375,15
B	16,26
C	0
D	-203
E	200

Result should be like this: (German 1.223 in UK format 1,223)

Sum

1.223

[michaeloneil]

Hi 

The result is wrong on this because of the values you entered contain incorrect characters.  Cell B2 uses a number of characters which, assuming this should be euro for Germany, I dont know if they are correct. Euro normally has a comma where a decimal is used in UK but i dont think i've seen a mix where the numbers are decimal and comma in the way B2 has. If you remove the trailing part of this value and leave it as -1.223 and also correct B3 to be 16.26 then your sum in B8 will be 12.037 not 1.223.

have you checked with the person providing the data that the values are correct? if they are then I would suggest formatting the cells to get what you need e.g. format B2 to use number with decimal 2 places. Or you could use BP to get the cell value, remove the extra characters then write it back to the cell. For B3 you could do a Find and Replace action to change the comma to a decimal.

Name	Value
A	-1.22
B	16.26
C	0.00
D	-203.00
E	200.00
Sum	12.03662485

[Anonymous]

Hi,

All values are in euros. Actually, the data comes from SAP in Excel format. As Excel and SAP are in German, the data is automatically formatted in German format. After I saved the data in the collection, the value is converted to a text format. I tried to convert the text into numbers with the function "ToConvert". Unfortunately, this did not work because of the data format. 

Finally, I managed to do it. But I am looking for any simplest idea if possible.

 

[MichealCharron · Accepted solution]

@Ekram Shihab 

Since you are pulling in the numbers as text, you can easily convert the German formatted numbers to numbers that you can work with by using the Replace function.

ToNumber(Replace(Replace([Data.Value],".",""), ",",".")) 

If you are open to using the original workbook to build your Sum and Rounding, you can use a neat trick with the "Write Collection" action in the Blue Prism "MS Excel VBO" VBO. 

1. Create a static collection with 1 field and 1 row. Put the following formula into that collection cell:

=ROUND(SUM(R2C:R[-1]C)/1000,0)

2. Find the next empty cell at the bottom of the column that you are working on using actions in the "MS Excel VBO" VBO.

3. Use the "Write Collection" action to write that collection to the cell you found in the previous step.

4. Use an action from the "MS Excel VBO" VBO to retrieve the value.

Basically, the "Write Collection" action can write R1C1 reference style formulas into a workbook (even if the R1C1 reference style formulas option is not selected). The formula above will sum all the numbers from the cell in the second row to the cell just above the formula).

[michael.wulf]

What the heck are you doing?

The thousands separator in German format is the dot, in English format it's a comma. Both separators are not important at all for doing math. 

Why don't you just replace the period with "nothing" and then the comma with the period? You would have the English format for numbers.