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.

 

[Joe4]

So just to clarify, the image that you posted in your original question, that is what you want it to look like in the end, right?
That is not what the data looks like originally, right? Originally it looks like a bunch of positive and negative numbers, right?

Or are your trying to go the other way, where the image in your original post is what you are starting with, and you want to convert it to positive and negative numbers?

And what column does this data exist in?
Do you want this conversion to happen in a new column (to the right), or in the same column?

 

[askall1000]

So just to clarify, the image that you posted in your original question, that is what you want it to look like in the end, right?
That is not what the data looks like originally, right? Originally it looks like a bunch of positive and negative numbers, right?

Or are your trying to go the other way, where the image in your original post is what you are starting with, and you want to convert it to positive and negative numbers?

And what column does this data exist in?
Do you want this conversion to happen in a new column (to the right), or in the same column?

The data i post is original, i want to convert numbers in that image as left aligned to positive, right alinged to negative.

 

[askall1000]

The data i post is original, i want to convert numbers in that image as left aligned to positive, right alinged to negative.

Sorry, data will be in N column and i want the new data in new column to the right will be appreciated.

 

[Joe4]

OK, here is some VBA code that should to that:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column N
    lr = Cells(Rows.Count, "N").End(xlUp).Row
    
'   Loop through all rows starting in row 2
    For r = 2 To lr
'       Check to see if cell is left-aligned
        If Cells(r, "N").HorizontalAlignment = xlLeft Then
'           Make positive number in column O
            Cells(r, "O").Value = Cells(r, "N")
        Else
'           Make negative number
            Cells(r, "O").Value = -Cells(r, "N")
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

And here is an example of what it did:

 

[askall1000]

OK, here is some VBA code that should to that:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column N
    lr = Cells(Rows.Count, "N").End(xlUp).Row
   
'   Loop through all rows starting in row 2
    For r = 2 To lr
'       Check to see if cell is left-aligned
        If Cells(r, "N").HorizontalAlignment = xlLeft Then
'           Make positive number in column O
            Cells(r, "O").Value = Cells(r, "N")
        Else
'           Make negative number
            Cells(r, "O").Value = -Cells(r, "N")
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

And here is an example of what it did:
View attachment 93386

Thank you very much, when i run the macro it gives mismatch error. I guess it would be better if i add excel file from my laptop after work, so it would be easier for you and i don't waste your time.

 

[Joe4]

Thank you very much, when i run the macro it gives mismatch error. I guess it would be better if i add excel file from my laptop after work, so it would be easier for you and i don't waste your time.

Yeah, it would be good to see how the data is structured. It is possible that instead of formatting, they entered as Text.

 

[askall1000]

Thank you very much, when i run the macro it gives mismatch error. I guess it would be better if i add excel file from my laptop after work, so it would be easier for you and i don't waste your time.

it works when i copied data to new workbook and run the macro. I will try to modify it in original; after N column i have data, maybe that is causing problem.

 

[askall1000]

Yeah, it would be good to see how the data is structured. It is possible that instead of formatting, they entered as Text.

When clean the data to the right and started it from 2nd row, it works!! Exactly do what i needed! Thank you very much for your help!!

 

[Joe4]

You are welcome.
Glad I was able to help!