Number to Text Convert Formula for 0 to 99

For Example: 2   - Two

22 - Two Two

58 - Five Eight

79 - Seven Nine

96 - Nine Six

Formula:

=IF(LEN(A1)=1,VLOOKUP(A1,{0,"Zero";1,"One";2,"Two";3,"Three";4,"Four";5,"Five";6,"Six";7,"Seven";8,"Eight";9,"Nine"},2,FALSE),VLOOKUP(VALUE(LEFT(A1,1)),{0,"Zero";1,"One";2,"Two";3,"Three";4,"Four";5,"Five";6,"Six";7,"Seven";8,"Eight";9,"Nine"},2,FALSE)&VLOOKUP(VALUE(RIGHT(A1,1)),{0,"Zero";1,"One";2,"Two";3,"Three";4,"Four";5,"Five";6,"Six";7,"Seven";8,"Eight";9,"Nine"},2,FALSE))

Staff Training Programme

                                                               Download Link

Numbers to Text Converting Formula

=TRIM(IF(A1<1000,"",IF(MOD(A1,1000000)>=100000,INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(A1,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(A1,100000)>=20000," "&INDEX({"";"";"Twenty";"Thirty";"Forty";"Fifty";"Sixty";"Seventy";"Eighty";"Ninety"},TRUNC(MOD(A1,100000)/10000,0)+1)&IF(MOD(MOD(A1,100000),10000)>=1000," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(MOD(A1,100000),10000)/1000,0)+1),""),IF(MOD(A1,100000)>=1000," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(A1,100000)/1000,0)+1),""))&" Thousand") & IF(A1<1,"Zero",IF(MOD(A1,1000)>=100," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(A1,1000)/100,0)+1)&" Hundred","")&IF(MOD(A1,100)>=20," "&INDEX({"";"";"Twenty";"Thirty";"Forty";"Fifty";"Sixty";"Seventy";"Eighty";"Ninety"},TRUNC(MOD(A1,100)/10,0)+1)&IF(MOD(MOD(A1,100),10)>=1," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(MOD(A1,100),10),0)+1),""),IF(MOD(A1,100)>=1," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(A1,100),0)+1),""))&""))

Download Link

Insert the rows in between the name list:

1. Add new "helper column"
2. Type "A"&"B" in helper columns cells
3. Select both cells & Drag it down
4. Data --> Filter ---> Select "A"
5. Select helper column
6. Press "Alt ;" to select only visible cells
7. Press Ctrl+Shift+Plus and Select "Entire Row" then Press OK
8. Data --> Filter ---> Select "B"
9. Select helper column
10. Press "Alt ;" to select only visible cells
11. Press Ctrl+Shift+Plus and Select "Entire Row" then Press OK

Check this link, http://www.screenr.com/J6mH

First-In, First-Out (FIFO) Method Calculation

Download Link

Highlight Duplicates

Sub ColorDuplicates()
'Color duplicate items between Sheet1.columns A and Sheet2.Column A
    For i = 1 To Sheets("Sheet1").Range("A65536").End(xlUp).Row
         If Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), Sheets("Sheet1").Range("A" & i)) = 1 Then
            With Sheets("Sheet1").Range("A" & i).Font
                .ColorIndex = 3
                .Bold = True
            End With
         Else
            With Sheets("Sheet1").Range("A" & i).Font
                .ColorIndex = 1
                .Bold = False
            End With
         End If
    Next i
    For i = 1 To Sheets("Sheet2").Range("A65536").End(xlUp).Row
         If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), Sheets("Sheet2").Range("A" & i)) = 1 Then
            With Sheets("Sheet2").Range("A" & i).Font
                .ColorIndex = 3
                .Bold = True
            End With
         Else
            With Sheets("Sheet2").Range("A" & i).Font
                .ColorIndex = 1
                .Bold = False
            End With
         End If
    Next i
End Sub

Download Link

Extracting Text from Alphanumeric Strings

=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8",""),"9","")

Download Link

Extracting Numbers from Alphanumeric Strings

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+SUM(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=".")))

Download Link

VBA Code for Insert Row

Sub InsRow()

Dim lastrow As Long, r As Long

lastrow = ActiveSheet.UsedRange.Rows.Count

For r = lastrow To 3 Step -1

If Cells(r, 1).Value <> "" Then Rows(r).Insert

Next r

End Sub

Download Link

AUTOMATIC QUOTATION FORMAT

Download Link

CCTV & SACS SYSTEM

Download Link

PMS CHECK LIST

Download Link

SERVICE SYSTEM

Download Link

AMC MANAGEMENT SYSTEM

Download Link

MTA MANAGEMENT SYSTEM

Download Link

AUTOMATIC INVOICE SYSTEM

 

Download Link 

VBA Code for Image Insert in Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Picture As Object
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
    Set Picture = Nothing
    On Error Resume Next
    Set Picture = Sheets("SMW").Pictures.Insert(ActiveWorkbook.Path & "\" & Target.Value & ".jpg")
    Picture.Top = .Top
            Picture.Left = .Left
            Picture.ShapeRange.LockAspectRatio = msoFalse
            Picture.Placement = xlMoveAndSize
            Picture.ShapeRange.Width = 50
            Picture.ShapeRange.Height = 44
End With
End Sub