Negative or Positve numbers according to alignment in cell

[askall1000]

Hello,
I need help on a formula or a solution without VBA that i can retrieve data to next column for example left aligned numbers must be postive right aligned numbers must be negative. Any help would be appreciated, and thanks in advance.

Please note that, i could not install xl2bb to company computer, so i could upload only as an image. And I am using Excel365.

 

[Micron]

You could try custom formats like #,###* ; -#,### ; 0* ;* @ or #,###* ; -#,###
The first one specifies format for text and zeros, neither which is considered +ve or -ve

 

[askall1000]

You could try custom formats like #,###* ; -#,### ; 0* ;* @ or #,###* ; -#,###
The first one specifies format for text and zeros, neither which is considered +ve or -ve

Thank you very much for your help. I copied your suggested format to cells. but it did not change the right alinged cells to negative. Probably I do something wrong, would you please elaborate further. I also copied image of format that i copied. I use dot for digit separator.

 

[Micron]

I misread your post I guess. I thought you wanted those numbers to become aligned based on their sign, not that you want to change the number sign based on alignment. If there is a non vba way to do that I will be amazed.

 

[Joe4]

You could do it in another column with a formula, basically by converting all the positive values to text, so they would be left-justified, i.e.

So the formula in cell C2 looks like this:

=IF(A2<0,A2,TEXT(A2,"General"))

If you want to hide the negative sign, like in your example, it would look like this:

=IF(A2<0,-A2,TEXT(A2,"General"))

Just note that by doing this, you have actually changed their values (negating and turning numbers into text).
So if you are trying to use these in mathematical computations, that could be problematic.

 

[Micron]

I should have added what I was thinking - "without changing the data type".

 

[Joe4]

I should have added what I was thinking - "without changing the data type".

Yeah, it certainly isn't the optimal way, but if it is just needed for presentation/visual needs, it would do the job.
Personally, I would probably go with VBA myself, as then you don't need to change the data type and can just apply formatting to the individual cells as needed.

 

[askall1000]

Hello Micron and Joe4, thank you very much for your time to reply my question. I might not clearly explained my problem; I need in another column or in same column, left aligned numbers as positive, and right aligned numbers with negative signs and used them in mathematical calculation. In original table they all positive numbers, just alignment differs their signs.

 

[Joe4]

In original table they all positive numbers, just alignment differs their signs.

So how exactly is this done in the original table?
Does each cell have its own separate formatting rules, as opposed to formatting the entire column the same way?

I cannot think of doing all the things that you require without using VBA (I am not saying it is impossible, I just cannot think of any way to do it, and the fact that no one else has any suggestions either suggests that it may not be possible without VBA).

So, I guess you have a choice to make - do you want to use VBA, or sacrifice some functionality you were hoping to have (i.e. I showed you a formulaic approach that would give you the visual effect you desire -- though some entries were not be interpretted as numbers, you may be able to coerce them back to perform calculations on them).

 

[askall1000]

So how exactly is this done in the original table?
Does each cell have its own separate formatting rules, as opposed to formatting the entire column the same way?

I cannot think of doing all the things that you require without using VBA (I am not saying it is impossible, I just cannot think of any way to do it, and the fact that no one else has any suggestions either suggests that it may not be possible without VBA).

So, I guess you have a choice to make - do you want to use VBA, or sacrifice some functionality you were hoping to have (i.e. I showed you a formulaic approach that would give you the visual effect you desire -- though some entries were not be interpretted as numbers, you may be able to coerce them back to perform calculations on them).

That data retrieve from dynamic AX old version, if it is not possible with formula, i can use help about VBA. Because i will need to do this operation periodically, so if you can do that with VBA, I appreciated your help. Thank you for your time.