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