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

Continue reading

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

Continue reading

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

Continue reading

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

Continue reading