Posts in category String Operations

Range Concatenation with a character

Jun29
2007 Leave a Comment Written by admin

Are you frustrated that you have to concatenate a range, and you have to do that using CONCATENATE formula by entering each cell and typing 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:

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

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

Posted in Useful Procedures - Tagged , ,

Text Search Count

Jun29
2007 Leave a Comment Written by admin

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

{=SUM(IF(ISERROR(SEARCH(D3,A2:A16)),0,1))}

Another using COUNTIF:

=COUNTIF(A2:A16,”*”&D3&”*”)

This picture might make it clear:

Tagged , ,

Add Specific number of zeros to a value

Feb08
2006 Leave a Comment Written by admin

This is one of the finest forumlae I’ve ever seen. Check this post at MrExcel.

If you want to have a value specific number of digits otherwise add zeros in front of it, this formula could be used:
=TEXT(A1,REPT(“0″,n))
where n is the number of digits you want.

So 4555 becomes 000004555 with formula =TEXT(4555,REPT(“0″,9))

Tagged

Random Text Generator

Oct23
2005 2 Comments Written by admin

This function will return random text (a-z) for the specified length. For ex. Randtxt(5) will return xythp.

‘—————————————————————————————
‘ Procedure : Randtxt
‘ DateTime  : 10/23/2005 11:54
‘ Author    : Ashutosh
‘ Purpose   :This function will return random text for the specified no. of chars

Function Randtxt(nochars As Byte) As String
    On Error GoTo Randtxt_Error
    If nochars = 0 Then Exit Function
    Dim sResult As String, i As Long
        sResult = “”
        For i = 1 To nochars
           sResult = sResult & Chr(Int(Rnd * (122 – 97) + 97))
        Next i
    Randtxt = sResult
    Exit Function
Randtxt_Error:
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in function Randtxt”
End Function

This sub will return random text for the specified length and number of random texts needed.

‘ Procedure : Randtxt1
‘ DateTime  : 10/23/2005 12:12
‘ Author    : Ashutosh
‘ Purpose   : This sub will return and no. of random text random text for the
‘specified no. of chars needed starting from the activecell
Sub Randtxt1()
Dim nochars As Byte
Dim nos As Long
nochars = InputBox(“Enter the length of the random text”, , 1)
nos = InputBox(“Enter the number the random texts needed”, , 1)
    On Error GoTo Randtxt_Error1
    If nochars = 0 Then Exit Sub
    Dim sResult As String, i As Long, j As Long
    For j = 1 To nos
        sResult = “”
        For i = 1 To nochars
           sResult = sResult & Chr(Int(Rnd * (122 – 97) + 97))
        Next i
            ActiveCell.Offset(j – 1, 0) = sResult
    Next j
    Exit Sub
Randtxt_Error1:
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & _
        ”) in procedure Randtxt”
End Sub
Tagged

String Operations

Mar09
2005 Leave a Comment Written by admin

I found this on microsoft’s website. I didn’t know that I can use MID function on the LHS and converting string to ASCII will speed up the process. Cool!!

“The following points provide suggestions for ways to enhance the performance of string operations:
Minimize concatenation operations when you can. You can use the Mid function on the left side of the equal sign to replace characters within the string, rather than concatenating them together. The drawback to using the Mid function is that the replacement string must be the same length as the substring you are replacing.

Dim strText As String

strText = ‘this is a test’
Mid(strText, 11, 4) = ‘tent’
Debug.Print strText

Microsoft Visual Basic for Applications (VBA) provides a number of intrinsic string constants that you can use to replace function calls. For example, you can use the vbCrLf constant to represent a carriage return/linefeed combination within a string, rather than using Chr(13) & Chr(10).
String-comparison operations are slow. Sometimes, you can avoid them by converting a character in the string to an ANSI value. For example, the following code checks whether the first character in a string is a space:

If Asc(strText) = 32 Then

The previous code is faster than the following:

If Left(strText, 1) = ‘ ‘ Then”
Tagged , , ,
« Older Entries Newer Entries »