Custom Format Cells Formulas

[banneduser123]

Hi,


I need the following to happen

column A contains numbers....
in column B, I am trying to input a formula for custom format: so that the numbers will appear as such

column A: 32,458,000 ; column B will show +32MM
column A: -100,000 ; column B will show -100K

im currently using the below formula, but it doesn't give me the + or - sign

[>=1000000] #,##0.00,,"MM";[>0] #,##0,"K";General


in column

 

[jtakw]

This formula would convert your numbers to look the way you want, I constructed the formula to round to 2 decimal points, you can change that as you wish, keep in mind the ending result is TEXT.

Excel 2010
	A	C
1	32458000	+32.46MM
2	-100000	-100K
3	110587	+110.59K
4	-1678400	-1.68MM
Sheet1

Cell Formulas
Range	Formula
C1	=IF(A1>1000000,"+"&0+ROUND(A1/1000000,2)&"MM",IF(A1>0,"+"&ROUND(A1/1000,2)&"K",IF(A1<-999999,ROUND(A1/1000000,2)&"MM",IF(A1<0,ROUND(A1/1000,2)&"K",A1))))

C1 formula copied down.

 

[Rick Rothstein]

You cannot Custom Format the cell the way you want, but you can use a macro that will look at the cell value and apply the appropriate number format for that value...

Sub KMformat()
  Dim Cell As Range, Pattern As String
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Cell.NumberFormat = "General"
    Select Case Cell.Value
      Case Is <= -1000000: Pattern = "0.00,,\M\M"
      Case Is <= -1000: Pattern = "0,\K"
      Case Is >= 1000000: Pattern = "+0.00,,\M\M"
      Case Is >= 1000: Pattern = "+0,\K"
    End Select
    Cell.NumberFormat = Pattern
  Next
End Sub

 

[banneduser123]

Thanks a lot guys. Will try tomorrow