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
