Negative sign at end of number string instead of beginning

[Dan K]

Hi All

I really hope this is an easy one and I am just missing something.

I get a text file emailed to me monthly from an accounting software which I need to convert to excel. I have no issues getting the data into a workable file but it is in excel I am having difficulty converting.

For example I use "," as number separators and "." as decimals. The format I receive is the opposite, "." as the number separator and "," as the decimal. I have written a quick macro to fix this but it is the negative numbers which have me stumped.

I will receive a number like 1.101,25- which I can convert to 1,101.25- but I do not know how to fix the negative format to read -1,101.25 without about a 5 step process of sorting, find & replace, then multiplying by a negative 1, then a copy paste of values.

I am sure there is an easier way to do this.

Please help.
Dan

 

[Sajan]

Hi Dan,
Are you looking for a macro solution for the negative sign suffix, or a formula solution?

-Sajan.

 

[Hui]

Dan K

Firstly, Welcome to the Chandoo.org forums

If the right character is a -'ve it is bound to be text
I would use:
=IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),A2)

 

[Dan K]

Dan K

Firstly, Welcome to the Chandoo.org forums

If the right character is a -'ve it is bound to be text
I would use:
=IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),A2)

Hi Hui

Mate you are a champion, thanks very much for the above formula works a treat, I knew there had to be an easier way to do things.

Have a good one.
Dan