Tuesday, October 23, 2007
Excel Training Handout
Recently, I conducted a training session for Ohio Association for Institutional Research and Planning (OAIRP). Here are the files that I used for the training:
Monday, September 17, 2007
Convert Text to Uppercase
If you want to convert the text to uppercase, use the following code; however, I recommend downloading ASAP Utilities, it has many functionalities, including text conversion. It doesn't offer source code though.
Here's my code for uppercase conversion:
(If you want to convert your text to lowercase, replace Ucase with Lcase function in the code)
Here's my code for uppercase conversion:
(If you want to convert your text to lowercase, replace Ucase with Lcase function in the code)
'Will convert selected range to Upper case, using array
Sub Conv2UCase()
On Error GoTo Conv2UCase_Error
Dim vDataArr As Variant
Dim lUpperBndRow As Long, lUpperBndCol As Long
Dim lRow As Long, lCol As Long
'store selected values in an array
vDataArr = Selection
'get the upper bound of rows
lUpperBndRow = UBound(vDataArr, 1)
'get the upper bound of cols
lUpperBndCol = UBound(vDataArr, 2)
'Start a loop to go through all the elements of the array
For lRow = 1 To lUpperBndRow
For lCol = 1 To lUpperBndCol
'Check if the value is text, if not don't convert
If WorksheetFunction.IsText(vDataArr(lRow, lCol)) Then
'Convert values to upper case
vDataArr(lRow, lCol) = UCase(vDataArr(lRow, lCol))
End If
Next lCol
Next lRow
'Return the converted values to the range
Selection = vDataArr
Exit Sub
Conv2UCase_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub:Conv2UCase"
End Sub
Sub Conv2UCase()
On Error GoTo Conv2UCase_Error
Dim vDataArr As Variant
Dim lUpperBndRow As Long, lUpperBndCol As Long
Dim lRow As Long, lCol As Long
'store selected values in an array
vDataArr = Selection
'get the upper bound of rows
lUpperBndRow = UBound(vDataArr, 1)
'get the upper bound of cols
lUpperBndCol = UBound(vDataArr, 2)
'Start a loop to go through all the elements of the array
For lRow = 1 To lUpperBndRow
For lCol = 1 To lUpperBndCol
'Check if the value is text, if not don't convert
If WorksheetFunction.IsText(vDataArr(lRow, lCol)) Then
'Convert values to upper case
vDataArr(lRow, lCol) = UCase(vDataArr(lRow, lCol))
End If
Next lCol
Next lRow
'Return the converted values to the range
Selection = vDataArr
Exit Sub
Conv2UCase_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub:Conv2UCase"
End Sub
Thursday, September 06, 2007
Excel Books
Books on my shelf: | |
| Professional Excel Development by Stephen Bullen, Rob Bovey This is by far the most authoritative book on Excel development from Add-ins to APIs. A word of caution though, it is not for the beginners. | |
| Excel Advanced Report Development by Timothy Zapawa Ever wondered on using Excel as a report development tool? This might be an answer to it; however, my problem with this book is that it focuses too much on pivottables, and because of that it doesn’t do justification to the name of the book. At any rate, this is very good book to exploit pivottable to its limits. | |
| Excel 2007 Power Programming with VBA by John Walkenbach Of course, it cannot happen that you talk about Excel books and don’t mention Mr. Spreadsheet himself. I am his fan on personal and professional level. On personal level, I love his blog, and on professional level, I like his lucid language in his books. I recommend reading his books and blog both. | |
| Excel 2003 Formulas by John Walkenbach You thought you knew formulas? Just read this book and you’ll realize, which I do almost every day, that there just so much about Excel that we don’t know. John uses simple language, and introduces the readers from basic to advanced formulas. How advance you might ask, well, here is an example, which uses a formula similar to this:  | |
| Excel 2007 Formulas by John Walkenbach | |
Books that I have Read: | |
| Data Analysis and Decision Making with Microsoft Excel by S. Christian Albright I borrowed this book from the library. It is a very good for beginners. | |
| Excel 2003 Power Programming with VBA by John Walkenbach | |
| Integrating Excel and Access by Michael Schmalz I borrowed this book from the library too. Excellent reference on developing applications using Access, Excel, and VBA. | |
| Mr Excel ON EXCEL by Bill Jelen Probably, this is my first book, which I read on Excel by Bill Jelen aka Mr. Excel. Fantastic book. If you have started knowing Excel just now, obtain a copy of this book right away. This is the right start for beginners. It uses very simple and clear language, and Bill provides great examples to make the learning useful. | |
| Guerilla Data Analysis Using Microsoft Excel by Bill Jelen I had an e-version of this book, but for some reason its not working anymore. Anyway, I had chewed this book a lot. Again, it’s a great book for starters, especially, in data crunching. |
Labels: Books
Thursday, August 16, 2007
A function to reverse a string
Very simple, uses the VBA function StrReverse to reverse the input string.
'A function to reverse a string provided as input
'For example, the string "abcd" will become "dcba"
'Uses the VBA function StrReverse
Public Function ReverseString(sInputString As String) As String
On Error GoTo ReverseString_Error
ReverseString = StrReverse(sInputString)
Exit Function
ReverseString_Error:
ReverseString = "#ERROR#"
End Function
'For example, the string "abcd" will become "dcba"
'Uses the VBA function StrReverse
Public Function ReverseString(sInputString As String) As String
On Error GoTo ReverseString_Error
ReverseString = StrReverse(sInputString)
Exit Function
ReverseString_Error:
ReverseString = "#ERROR#"
End Function
Labels: String Operations
Tuesday, July 24, 2007
Concatenate function
Oh, man, I can't tell how useful that concatenate function is.
One repetitive use I found is to create OR/AND conditions for Access queries. I copy-paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concatfunc to create a string to use in my Access query.
For example, look at this print screen:
The Range A1:A4 houses the string condition I want to use in my Access query to restrict the fruits from my data. In cell B1 I have the formula
Now, all you have to do is copy and paste this in Access criteria and put a quotation mark at the start and at the end of this string.
I have found one more use of this when I want to store some values in an Array, using the Array function in VBA.
One more print screen:
In this example, I insert a comma (CHAR(44) instead of string OR, and this function returns a string that I can use in VBA to store these values in an array using Array function, after adding a quotation mark, of course, at the start and at the end.
One repetitive use I found is to create OR/AND conditions for Access queries. I copy-paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concatfunc to create a string to use in my Access query.
For example, look at this print screen:
The Range A1:A4 houses the string condition I want to use in my Access query to restrict the fruits from my data. In cell B1 I have the formula
=PERSONAL.XLS!concatfunc(A1:A4,CHAR(34) & " or " & CHAR(34))
, and the return string from this function is listed in cell B1.Now, all you have to do is copy and paste this in Access criteria and put a quotation mark at the start and at the end of this string.
I have found one more use of this when I want to store some values in an Array, using the Array function in VBA.
One more print screen:
In this example, I insert a comma (CHAR(44) instead of string OR, and this function returns a string that I can use in VBA to store these values in an array using Array function, after adding a quotation mark, of course, at the start and at the end.
Labels: String Operations
Print Structures of dbf files in separate workbooks
Recently, I had the need of printing the data structures of many dbf files. A google search reveled the DBF.COM file that would output dbf file structures, number of records, file name, date of last update, etc.
Download this file here: files/DBF.COM (Caution: I downloaded this file from the internet, and I do not make any warranty of it being spyware or malicious ware free. Use it on your own risk.)
To use this file go to Start>Run>cmd and in DOS type:
Note: cd is used to change the current directory
Here's a print screen:
This will create a file myfilestru.txt in the same directory where your dbf files are residing. This text file will look like this:
Now, that we have a text file all the file structures, we need to create separate workbooks for each dbf file. To do that open this text file in Excel, press Alt + F11 to view the VBA window, double-click the sheet name, and paste this code. After that run this code, and you should have separate workbooks for each dbf file.
Download this file here: files/DBF.COM (Caution: I downloaded this file from the internet, and I do not make any warranty of it being spyware or malicious ware free. Use it on your own risk.)
To use this file go to Start>Run>cmd and in DOS type:
cd C:/
dbf.com > myfilestru.txt
dbf.com > myfilestru.txt
Note: cd is used to change the current directory
Here's a print screen:
This will create a file myfilestru.txt in the same directory where your dbf files are residing. This text file will look like this:
Now, that we have a text file all the file structures, we need to create separate workbooks for each dbf file. To do that open this text file in Excel, press Alt + F11 to view the VBA window, double-click the sheet name, and paste this code. After that run this code, and you should have separate workbooks for each dbf file.
Sub FormatThisFile()
'Define some variables
Dim iLastRow As Integer, i As Integer, sWrkbkNm As String, sNewWrkbkNm As String
Dim iPrevLastRow As Integer, iFirstRow As Integer, iNewWrkbkRow As Integer
On Error GoTo FormatThisFile_Error
'Turn off the screen update to run the macro faster
Application.ScreenUpdating = False
'Refer to the current workbook and sheet1
With ThisWorkbook.Sheets(1)
'get the last row number
iLastRow = .Range("A65536").End(xlUp).Row
i = 1
Do While i < iLastRow
'search for string "Structure for"
If InStr(1, .Range("A" & i), "Structure for") > 0 Then
'if found then store the file name
sWrkbkNm = Trim(Right(.Range("A" & i), Len(.Range("A" & i)) - Application.WorksheetFunction.Find(":", .Range("A" & i))))
sWrkbkNm = Left(sWrkbkNm, Len(sWrkbkNm) - 4)
iFirstRow = i
'search for string "** Total **"
ElseIf InStr(1, .Range("A" & i), "** Total **") > 0 Then
iPrevLastRow = i
'add a workbook
Workbooks.Add
'save it
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sWrkbkNm
sNewWrkbkNm = sWrkbkNm & ".xls"
'with this new workbook do copy-paste
With Workbooks(sNewWrkbkNm)
ThisWorkbook.Sheets(1).Range("A" & iFirstRow & ":" & "A" & iPrevLastRow).Copy Destination:= _
.Sheets(1).Range("A1")
iNewWrkbkRow = .Sheets(1).Range("A65536").End(xlUp).Row
.Sheets(1).Range("A4:A" & iNewWrkbkRow).TextToColumns Destination:= _
.Sheets(1).Range("A4"), DataType:=xlDelimited, Space:=True
.Sheets(1).Range("A5:A" & iNewWrkbkRow - 1).Delete Shift:=xlToLeft
.Sheets(1).Columns("A:IV").EntireColumn.AutoFit
.Sheets(1).Columns("A:IV").WrapText = False
'save and close this workbook
.Save
.Close
End With
End If
i = i + 1
Loop
MsgBox "Done"
End With
On Error GoTo 0
SmoothExit_FormatThisFile:
Application.ScreenUpdating = True
Exit Sub
FormatThisFile_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FormatThisFile"
Resume SmoothExit_FormatThisFile
End Sub
'Define some variables
Dim iLastRow As Integer, i As Integer, sWrkbkNm As String, sNewWrkbkNm As String
Dim iPrevLastRow As Integer, iFirstRow As Integer, iNewWrkbkRow As Integer
On Error GoTo FormatThisFile_Error
'Turn off the screen update to run the macro faster
Application.ScreenUpdating = False
'Refer to the current workbook and sheet1
With ThisWorkbook.Sheets(1)
'get the last row number
iLastRow = .Range("A65536").End(xlUp).Row
i = 1
Do While i < iLastRow
'search for string "Structure for"
If InStr(1, .Range("A" & i), "Structure for") > 0 Then
'if found then store the file name
sWrkbkNm = Trim(Right(.Range("A" & i), Len(.Range("A" & i)) - Application.WorksheetFunction.Find(":", .Range("A" & i))))
sWrkbkNm = Left(sWrkbkNm, Len(sWrkbkNm) - 4)
iFirstRow = i
'search for string "** Total **"
ElseIf InStr(1, .Range("A" & i), "** Total **") > 0 Then
iPrevLastRow = i
'add a workbook
Workbooks.Add
'save it
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sWrkbkNm
sNewWrkbkNm = sWrkbkNm & ".xls"
'with this new workbook do copy-paste
With Workbooks(sNewWrkbkNm)
ThisWorkbook.Sheets(1).Range("A" & iFirstRow & ":" & "A" & iPrevLastRow).Copy Destination:= _
.Sheets(1).Range("A1")
iNewWrkbkRow = .Sheets(1).Range("A65536").End(xlUp).Row
.Sheets(1).Range("A4:A" & iNewWrkbkRow).TextToColumns Destination:= _
.Sheets(1).Range("A4"), DataType:=xlDelimited, Space:=True
.Sheets(1).Range("A5:A" & iNewWrkbkRow - 1).Delete Shift:=xlToLeft
.Sheets(1).Columns("A:IV").EntireColumn.AutoFit
.Sheets(1).Columns("A:IV").WrapText = False
'save and close this workbook
.Save
.Close
End With
End If
i = i + 1
Loop
MsgBox "Done"
End With
On Error GoTo 0
SmoothExit_FormatThisFile:
Application.ScreenUpdating = True
Exit Sub
FormatThisFile_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FormatThisFile"
Resume SmoothExit_FormatThisFile
End Sub
Wednesday, July 18, 2007
Access VBA: Convert Access tables to arff format
Weka, an open source data mining software, uses arff input data format. You can use this code to convert any Access table to arff format. Download the Access database with code: Convert2Arff.mdb
This can very well be designed using forms, but this should get one started. Use Alt + F11 to see the code, then execute procedure ConvertTbl2Arff to convert a table.
Some highlights of this procedure
- Takes care of spaces in Attribute name and data values
- Finds unique values of nominal variables
- Assigns equivalent ARFF datatype
- Replaces missing values with question marks
This can very well be designed using forms, but this should get one started. Use Alt + F11 to see the code, then execute procedure ConvertTbl2Arff to convert a table.
Some highlights of this procedure
- Takes care of spaces in Attribute name and data values
- Finds unique values of nominal variables
- Assigns equivalent ARFF datatype
- Replaces missing values with question marks
Labels: Access
Access VBA: Export Access tables using ODBC
If you want to export Access tables using ODBC/DSN connections, use the following code. This procedure uses the File DSN and ODBC connection to export Access tables using DAO object TableDef.
Sub ExportTbls()
Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef
On Error GoTo ExportTbls_Error
sTypExprt = "ODBC Database" 'Export Type
sCnxnStr = "ODBC;DSN=DSNName;UID=userID;PWD=password" 'Create the connection string
vStTime = Timer
Application.Echo False, "Visual Basic code is executing."
Set db = CurrentDb()
'need a reference to Microsoft DAO 3.x library
For Each tbldef In db.TableDefs
'Don't export System and temporary tables
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 4) <> "~TMP" Then
'Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
End If
Next tbldef
MsgBox "Done! Time taken=" & Timer - vStTime
On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub
ExportTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
Resume SmoothExit_ExportTbls
End Sub
Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef
On Error GoTo ExportTbls_Error
sTypExprt = "ODBC Database" 'Export Type
sCnxnStr = "ODBC;DSN=DSNName;UID=userID;PWD=password" 'Create the connection string
vStTime = Timer
Application.Echo False, "Visual Basic code is executing."
Set db = CurrentDb()
'need a reference to Microsoft DAO 3.x library
For Each tbldef In db.TableDefs
'Don't export System and temporary tables
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 4) <> "~TMP" Then
'Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
End If
Next tbldef
MsgBox "Done! Time taken=" & Timer - vStTime
On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub
ExportTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
Resume SmoothExit_ExportTbls
End Sub
Labels: Access
Access VBA: Delete tables from Access database
If you want to delete all or some of the tables from your Access database, you can use this DAO approach. You would need a reference to Microsoft DAO 3.x object library. As shown in the example, you can use an array to store the table names, which you want to keep or delete.
Sub DelteTbls()
Dim sTblNm As String
Dim db As Database, tbldef As DAO.TableDef
Dim i As Integer, Arr As Variant
On Error GoTo DelteTbls_Error
'You can use an array if you want to delete or not delete specific tables
'Arr = Array("Table1","Table2","Table3")
Set db = CurrentDb() 'Set the database object
'Set the warnings off to suppress messages
DoCmd.SetWarnings False
'For i = 0 To UBound(Arr)
For Each tbldef In db.TableDefs
'here you can use equal to or not equal to delete or keep specific tables
'If Left(tbldef.Name, 4) = Arr(i) Then
'Don't delete System or temporary tables
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 1) <> "~" Then
Debug.Print tbldef.Name
sTblNm = tbldef.Name
'Delete table
DoCmd.DeleteObject acTable, sTblNm
End If
Next tbldef
'Next i
MsgBox "Done!"
On Error GoTo 0
SmoothExit_DelteTbls:
Set db = Nothing
DoCmd.SetWarnings True
Exit Sub
DelteTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DelteTbls"
Resume SmoothExit_DelteTbls
End Sub
Dim sTblNm As String
Dim db As Database, tbldef As DAO.TableDef
Dim i As Integer, Arr As Variant
On Error GoTo DelteTbls_Error
'You can use an array if you want to delete or not delete specific tables
'Arr = Array("Table1","Table2","Table3")
Set db = CurrentDb() 'Set the database object
'Set the warnings off to suppress messages
DoCmd.SetWarnings False
'For i = 0 To UBound(Arr)
For Each tbldef In db.TableDefs
'here you can use equal to or not equal to delete or keep specific tables
'If Left(tbldef.Name, 4) = Arr(i) Then
'Don't delete System or temporary tables
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 1) <> "~" Then
Debug.Print tbldef.Name
sTblNm = tbldef.Name
'Delete table
DoCmd.DeleteObject acTable, sTblNm
End If
Next tbldef
'Next i
MsgBox "Done!"
On Error GoTo 0
SmoothExit_DelteTbls:
Set db = Nothing
DoCmd.SetWarnings True
Exit Sub
DelteTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DelteTbls"
Resume SmoothExit_DelteTbls
End Sub
Labels: Access
Access VBA: Link all Dbase files from a folder
If you would like to link all Dbase files, any linkable file for that matter, in MS Access, use the following code. I read somewhere that refreshing the links is slower than deleting and creating new links.
Sub LinkAllTblsinDir()
Dim sTblNm As String, sPath As String, sFileNm As String
sPath = "C:\DW\"
'Turn of the Echo to avoid window repaint/refresh
Application.Echo False
sFileNm = Dir(sPath, vbNormal)
Do While sFileNm <> ""
If Right(sFileNm, 3) = "dbf" Then
sTblNm = Left(sFileNm, Len(sFileNm) - 4) 'Extract the file name
'Use the TransferDatabase option to link the tables from the specified directory
'to your current Access DB
DoCmd.TransferDatabase acLink, "dBase III", sPath, acTable, sTblNm, sTblNm
End If
sFileNm = Dir
Loop
Application.Echo True
End Sub
Dim sTblNm As String, sPath As String, sFileNm As String
sPath = "C:\DW\"
'Turn of the Echo to avoid window repaint/refresh
Application.Echo False
sFileNm = Dir(sPath, vbNormal)
Do While sFileNm <> ""
If Right(sFileNm, 3) = "dbf" Then
sTblNm = Left(sFileNm, Len(sFileNm) - 4) 'Extract the file name
'Use the TransferDatabase option to link the tables from the specified directory
'to your current Access DB
DoCmd.TransferDatabase acLink, "dBase III", sPath, acTable, sTblNm, sTblNm
End If
sFileNm = Dir
Loop
Application.Echo True
End Sub
Labels: Access
Get file names from a directory
If you want get or print file names from a certain directory, then you can use following code.
Sub GetFileNames()
Dim sPath As String, sFileNm As String
sPath = "C:\DW\"
'You can also use Application.GetOpenFilename to get a file name from a folder,
'and then extract the Directory name from that string
'You can also use filters with GetOpenFilenam such as *.txt, see Help on this topic
sFileNm = Dir(sPath, vbNormal) 'Get the first file from the specified directory
'Start a loop
Do While sFileNm <> ""
'If the file has a dbf extension then print the file name
If Right(sFileNm, 3) = "dbf" Then
Debug.Print sFileNm
End If
sFileNm = Dir
Loop
End Sub
Sub GetFileNames()
Dim sPath As String, sFileNm As String
sPath = "C:\DW\"
'You can also use Application.GetOpenFilename to get a file name from a folder,
'and then extract the Directory name from that string
'You can also use filters with GetOpenFilenam such as *.txt, see Help on this topic
sFileNm = Dir(sPath, vbNormal) 'Get the first file from the specified directory
'Start a loop
Do While sFileNm <> ""
'If the file has a dbf extension then print the file name
If Right(sFileNm, 3) = "dbf" Then
Debug.Print sFileNm
End If
sFileNm = Dir
Loop
End Sub
Friday, June 29, 2007
Range Concatenation with a character
Are you frustrated because you have to concatenate a range, and you have to do that using CONCATENATE formula by entering each cell and putting a comma after every cell? Well, here's a solution to it. A procedure or a function whatever you like. If you use the procedure, it allows you to choose the input range, concatenate character, and the output range. If you use the function, then you can enter the optional concatenate character (by default it is a comma (,)), and the input range.
Here are both:
Procedure:
Here's the function:
Here's an example:
Here are both:
Procedure:
Public Sub ConCatwChar()
Dim sChar2bAdded As String, rngRng2bCated As Range, sOutput As String, rngTarget As Range, c As Range
On Error GoTo ConCatwChar_Error
'You could use this line to return the concatenated string in this cell
'Set rngTarget = ActiveCell
Set rngRng2bCated = Application.InputBox(prompt:="Select the range you'd like to concatenate with a charcter", _
Title:="Select Range", Type:=8)
If rngRng2bCated Is Nothing Then Exit Sub
'You could use this line to set the default to a comma and remove the inputbox line
'sChar2bAdded = ","
sChar2bAdded = InputBox("Enter the character you'd like to add between other cells", "Enter Character", ",")
Set rngTarget = Application.InputBox(prompt:="Select the range you'd like the output", _
Title:="Select Range", Type:=8)
For Each c In rngRng2bCated
sOutput = sOutput & c.Value & sChar2bAdded
Next c
sOutput = Left(sOutput, Len(sOutput) - Len(sChar2bAdded))
rngTarget = sOutput
On Error GoTo 0
Exit Sub
ConCatwChar_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ConCatwChar"
End Sub
Dim sChar2bAdded As String, rngRng2bCated As Range, sOutput As String, rngTarget As Range, c As Range
On Error GoTo ConCatwChar_Error
'You could use this line to return the concatenated string in this cell
'Set rngTarget = ActiveCell
Set rngRng2bCated = Application.InputBox(prompt:="Select the range you'd like to concatenate with a charcter", _
Title:="Select Range", Type:=8)
If rngRng2bCated Is Nothing Then Exit Sub
'You could use this line to set the default to a comma and remove the inputbox line
'sChar2bAdded = ","
sChar2bAdded = InputBox("Enter the character you'd like to add between other cells", "Enter Character", ",")
Set rngTarget = Application.InputBox(prompt:="Select the range you'd like the output", _
Title:="Select Range", Type:=8)
For Each c In rngRng2bCated
sOutput = sOutput & c.Value & sChar2bAdded
Next c
sOutput = Left(sOutput, Len(sOutput) - Len(sChar2bAdded))
rngTarget = sOutput
On Error GoTo 0
Exit Sub
ConCatwChar_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ConCatwChar"
End Sub
Here's the function:
'You can change the option string character to nothing "" so that you get concatenated string without a character in between
Public Function ConCatFunc(rngRng2bCated As Range, Optional sChar2bAdded As String = ",") As String
Dim sOutput As String, c As Range
On Error GoTo ConCatFunc_Error
For Each c In rngRng2bCated
sOutput = sOutput & c.Value & sChar2bAdded
Next c
sOutput = Left(sOutput, Len(sOutput) - Len(sChar2bAdded))
ConCatFunc = sOutput
On Error GoTo 0
Exit Function
ConCatFunc_Error:
ConCatFunc = "#Error#"
End Function
Public Function ConCatFunc(rngRng2bCated As Range, Optional sChar2bAdded As String = ",") As String
Dim sOutput As String, c As Range
On Error GoTo ConCatFunc_Error
For Each c In rngRng2bCated
sOutput = sOutput & c.Value & sChar2bAdded
Next c
sOutput = Left(sOutput, Len(sOutput) - Len(sChar2bAdded))
ConCatFunc = sOutput
On Error GoTo 0
Exit Function
ConCatFunc_Error:
ConCatFunc = "#Error#"
End Function
Here's an example:
Labels: String Operations
Text Search Count
If you want to do a text search count in a range, you can use the following formulas:
Where D3 is the search text and the range A2:A16 houses the data.
An array formula using SUM and SEARCH
Another using COUNTIF:
This picture might make it clear:
Where D3 is the search text and the range A2:A16 houses the data.
An array formula using SUM and SEARCH
{=SUM(IF(ISERROR(SEARCH(D3,A2:A16)),0,1))}
Another using COUNTIF:
=COUNTIF(A2:A16,"*"&D3&"*")
This picture might make it clear:
Labels: String Operations
