• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cells formatting

I

igorsusa

Member
Hey guys,

I have one easy one for you.

So, one of my business partner just migrated to office 2019 and we are facing small issue with our Excel file.

Problem is in Sheet "Komercialni promet", rows 55-56. Values are copied with macro in these rows and then should be counted in row 17. As you see in cell F17 there is no value.

For some reason the code with office 2019 is not copying data as numbers, so formula in row 17 is not showing any values. I tried to convert and format cells to numbers, but he is experiencing same issue.

Just to give you short background. Partner is just copying data from one website into RED sheets. After that, they click on month in row 1 (in sheet Komercialn promer) and data is copied for that month.

Attached the file.

Thank you.
 

Attachments

  • chandoo - question.xlsm
    229.3 KB · Views: 2
Pasted values are in Euro format. Meaning it uses period "." as thousand separator and comma "," as decimal separator. Thus, in US system where these separators are reversed, values are considered text and not numeric value. And mathematical function will not work.

To fix, you can use FIND & REPLACE function (CTRL + H).

Select F55 & 56, hit CTRL + H.
First replace period with "@". Then replace comma with period. Then replace "@" with comma.

Or override system separator for Excel (Options -> Advanced -> Editing Options)
59920
 
Last edited:
Hey Chiciro.

Thanks for comment. I think easer would be option B.

I changed decimal seperatior to "." and thousand seperator to "," --> Should I tick as well "use system seperators" ?

59921

After this I see that there is no more error message in cells F55-56, which is probably good news.

59922

But formula in F17 is still not calculating.

59923
 
You'll need to confirm entry in F55 & 56 (i.e. enter the cell and hit enter). Since, it's currently stored as text type.

Edit: Oh, and you can't tick system checkbox, unless your system also uses same regional setting for the separators. I assume it doesn't, since you had the issue described.
 
You must log in or register to reply here.
Back
Top