Unable to identify the greater number

[Alfredhigh]

Dear Sir/Mdm,



I used the "Conditional Formatting" to format the cell to greater than, but why the three cell(circle in red) didn't change color?

 

[Joe4]

Welcome to the Board!

I see those numbers have "B"s and "M"s at the end of them.
Are they really numbers with some special formatting applied to them, or are they text entries?
The easiest way to check is to try changing the format of those cells to "General" and see if the appearance of those numbers change.
If they do not, then you are dealing with Text entries, which is going to make it more difficult to try to do numeric checks/calculations on.

Also, please post the Conditional Formatting Rules you are using.

 

[Alfredhigh]

Welcome to the Board!

I see those numbers have "B"s and "M"s at the end of them.
Are they really numbers with some special formatting applied to them, or are they text entries?
The easiest way to check is to try changing the format of those cells to "General" and see if the appearance of those numbers change.
If they do not, then you are dealing with Text entries, which is going to make it more difficult to try to do numeric checks/calculations on.

Also, please post the Conditional Formatting Rules you are using.

I have tried change to "General" the "B" and "M" still the same.

 

[Joe4]

So, the issue is that you are dealing with text entries, not numeric ones, and that is why you cannot do numeric mathematical computations on them.
Any chance you can convert those entries to numbers?

I am guessing that "M" stands for millions and "B" stands for billions.
If you cannot get them to send you the data using real numbers, we could probably come up with some VBA code to convert those to valid numbers for you.

 

[Alfredhigh]

So, the issue is that you are dealing with text entries, not numeric ones, and that is why you cannot do numeric mathematical computations on them.
Any chance you can convert those entries to numbers?

I am guessing that "M" stands for millions and "B" stands for billions.
If you cannot get them to send you the data using real numbers, we could probably come up with some VBA code to convert those to valid numbers for you.

Where to get the VBA code?

 

[Joe4]

Here is VBA code that will work.
Just select all the cells in the range that need converting, and run this VBA code:

Sub ConvertNums()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Run against every cell in selected range
    For Each cell In Selection
        Select Case Right(cell, 1)
            Case "M"
                cell.Value = Left(cell, Len(cell) - 1) * 1000000
            Case "B"
                cell.Value = Left(cell, Len(cell) - 1) * 1000000000
        End Select
    Next cell
    
    Application.ScreenUpdating = True
        
End Sub

If you aren't sure how to insert or run VBA code, see here: Insert and run VBA macros in Excel - step-by-step guide.)

 

[Alfredhigh]

Here is VBA code that will work.
Just select all the cells in the range that need converting, and run this VBA code:

Sub ConvertNums()

    Dim cell As Range
   
    Application.ScreenUpdating = False
   
'   Run against every cell in selected range
    For Each cell In Selection
        Select Case Right(cell, 1)
            Case "M"
                cell.Value = Left(cell, Len(cell) - 1) * 1000000
            Case "B"
                cell.Value = Left(cell, Len(cell) - 1) * 1000000000
        End Select
    Next cell
   
    Application.ScreenUpdating = True
       
End Sub

If you aren't sure how to insert or run VBA code, see here: Insert and run VBA macros in Excel - step-by-step guide.)

Hi Joe4,

Need your help after I inserted the VBA code, the cell value appeared abnormal, I have circle in blue.

 

[Joe4]

That is a valid number.
That is just Excel displaying the number in Scientific Notation.
See: Excel tutorial: How to use scientific formatting in Excel.

 

[Alfredhigh]

That is a valid number.
That is just Excel displaying the number in Scientific Notation.
See: Excel tutorial: How to use scientific formatting in Excel.

Hi Joe4,

But I don't want it to be Scientific Notation.

 

[Joe4]

Hi Joe4,

But I don't want it to be Scientific Notation.

That is controlled by the format you have on that cell.
Just change it off the "Scientific" format to "General" or "Number", and it will appear the way you want.