text to number macro and unsolvable problem

[debou7001]

Hi everybody,

please have a look on my file, i'm trying to convert some data and i'm facing few inexplicable problem.

I have allready try many solution but it is not working as i want.

in the best before and after you will see the data. many of them are well converted with different solution that i tried in my macro but some cell it doesn't work automatically. i have tried column to text, paste special, substitute but nothing working well.

is somebody could explain me what's is happening?

thanks

@Faraz Shaikh ?? :)

[#2]

Hi @debou7001,

As far as I understand looking at the file, you are trying to convert the decimal value for "," into a "."

You can convert the same following

1. Power Query changing the data type

2. Writing the formula in the cell as showed in each screenshot

I'm attaching the file you can have a look

Hope this is workable for you.

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

[#3]

Thanks Faraz,

Unfortunatly it seems that it is more complicated. When i open my original file i see all value as text. I try to find a solution to convert them as number or as date. But there is some crazy things. Some Column are well converted ( At that time i use texttocolumn in a loop to convert them) but in some column sometime some value are not well converted in number or they keep the . as the decimal separator at least the coma. than 1.500 is not 1,500. i'm trying to find a way to fix that.I want to force to use regional setting but i dont understand why it is not working too.
best regards. and again thanks for your previous reply.

[#4]

@debou7001 

With Text to Columns you shall select proper separators on the third step of the wizard. As they are in source data, not in your locale.