Count of Words in a Cell by UDF

Function for counting words in a cell excluding space (ready vba code just copy , paste and run)

Function COUNTWORDS(Text_String As String) As Integer
'User define function counts the number of words in a cell exclude space
'www.anilnahar.com
String_Length = Len(Text_String)
For Current_Character = 1 To String_Length
COUNTWORDS = 0
Dim String_Length As Integer
Dim Current_Character As Integer
If (Mid(Text_String, Current_Character, 1)) = " " Then
COUNTWORDS = COUNTWORDS + 1
End If
Next Current_Character
End Function

Alphabets Serial Capital And Small Letter

Sub Alphabets_capital()
‘ Smart code for Serial Alphabets in Capital Letters from select cell
‘ Smart Excel (www.anilnahar.com)
Dim i As Integer
For i = 65 To 90
ActiveCell.Value = Chr(i)
ActiveCell.Offset(1, 0).Select
Next i
End Sub
—————
Sub Alphabets_small()
‘ Smart code for Serial Alphabates in Small Letters from select cell
‘ Smart Excel(anilnahar.com)
Dim i As Integer
For i = 97 To 122
ActiveCell.Value = Chr(i)
ActiveCell.Offset(1, 0).Select
Next i
End Sub

Insert Multiple Text file in Worksheet ready vba code

Sub MulipleTextFiles()
‘SmartCode for Insert Multiple text file in worksheets
‘SmartExcel(www.anilnahar.com)

Dim xFilesToOpen As Variant
Dim I As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
On Error GoTo ErrHandler
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
xDelimiter = “|”
xFilesToOpen = Application.GetOpenFilename(“Text Files (*.txt), *.txt”, , “Smart Excel”, , True)
If TypeName(xFilesToOpen) = “Boolean” Then
MsgBox “No files were selected”, , “Smart Excel”
GoTo ExitHandler
End If
I = 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Copy
Set xWb = Application.ActiveWorkbook
xTempWb.Close False
xWb.Worksheets(I).Columns(“A:A”).TextToColumns _
Destination:=Range(“A1″), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=”|”
Do While I < UBound(xFilesToOpen)
I = I + 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
With xWb
xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
.Worksheets(I).Columns(“A:A”).TextToColumns _
Destination:=Range(“A1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=xDelimiter
End With
Loop
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , “SmartExcel(www.anilnahar.com)”
Resume ExitHandler
End Sub