# Category Archives for "String Operations"

## Concatenate range of cells with a delimiter

Problem Are you frustrated that you have to concatenate a big range of cells in Excel, but you have to do so using CONCATENATE function 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, […]

## 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 {=SUM(IF(ISERROR(SEARCH(D3,A2:A16)),0,1))} Another using COUNTIF: =COUNTIF(A2:A16,”*”&D3&”*”) This picture might make it clear:

## Add Specific number of zeros to a value

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))

## 2 Random Text Generator

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 […]

## String Operations

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 […]