How to REPLACE any substrings or COUNT them in a string

Let's say we are dealing with the following string entered in cell A1:

"Examples of replacing or counting any string elements in a cell"

To replace e.g. the 3rd letter 'a' with 'A' in the string, we would use this formula:

=SUBSTITUTE($A$1,"a","A",3)

To count e.g. the number of SPACES in the string, the following formula can be used:

=LEN($A$1) -LEN(SUBSTITUTE($A$1," ",""))        (result = 10)

To count the number of substrings, such as e.g. "e", "in", "le" or "count" in the string, the following formulas can be used:

• =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"e","")))/LEN("e")    (result = 6)
• =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"in","")))/LEN("in")    (result = 4)
• =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"le","")))/LEN("le")    (result = 2)
• =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"count","")))/LEN("count")     (result = 1)

 

Template for Basic Data Summary/Analysis

If you have collected and organized some data in table(s) or list(s), the first thing you might need could be to get quick general analysis/evaluation of that data - before going any further with some math, statistical or graphic analysis.

The following example provides easy solution, kind of a template, for that preliminary summary and basic analysis.

Here's just illustrative table of random data. Let's say you want to know "everything" about this set of values.

How to design colorful cell contents in Excel

If you need to design a colorful title or banner or something similar, so that every character or group of characters or digits has a distinct color, then you can find this post helpful. Here are just two simple examples of effects you can achieve:

You can easily produce such effects quickly by entering your text (including also digits) in any cell of your worksheet, selecting the cell, and running the macro listed below. Just remember that if your cell contains just a number it must be formatted as text for this purpose.

The macro can be entered/copied into any VBA module of your workbook. Obviously, it can be modified as needed for your specific needs. Enjoy!

Sub clrFonts()
'Colors every character within a string of selected cell
Dim cnt As String
Dim rng As Range
Dim n As Long
Set rng = ActiveCell
For n = 1 To Len(rng.Value)
    cnt = Mid(rng.Value, n, 1)
    If cnt = "a" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "b" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 0): GoTo cont 'green
    If cnt = "c" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 0): GoTo cont 'red
    If cnt = "d" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "e" Then rng.Characters(n, 1).Font.Color = RGB(153, 51, 0): GoTo cont 'brownish
    If cnt = "f" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 255): GoTo cont 'd red
    If cnt = "g" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 255): GoTo cont 'green blue
    If cnt = "h" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 0): GoTo cont 'brown
    If cnt = "i" Then rng.Characters(n, 1).Font.Color = RGB(0, 128, 0): GoTo cont 'vd green
    If cnt = "j" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 128): GoTo cont 'd blue
    If cnt = "k" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 0): GoTo cont 'd grey
    If cnt = "l" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 128): GoTo cont 'vd brown
    If cnt = "m" Then rng.Characters(n, 1).Font.Color = RGB(192, 192, 192): GoTo cont 'l grey
    If cnt = "n" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 128): GoTo cont 'l green
    If cnt = "o" Then rng.Characters(n, 1).Font.Color = RGB(153, 153, 255): GoTo cont 'l blue
    If cnt = "p" Then rng.Characters(n, 1).Font.Color = RGB(153, 51, 102): GoTo cont 'vvd brown
    If cnt = "q" Then rng.Characters(n, 1).Font.Color = RGB(255, 255, 204): GoTo cont 'vl yellow
    If cnt = "r" Then rng.Characters(n, 1).Font.Color = RGB(51, 153, 102): GoTo cont 'green blue
    If cnt = "s" Then rng.Characters(n, 1).Font.Color = RGB(102, 0, 102): GoTo cont 'vvvd brown
    If cnt = "t" Then rng.Characters(n, 1).Font.Color = RGB(255, 128, 128): GoTo cont 'd orange
    If cnt = "u" Then rng.Characters(n, 1).Font.Color = RGB(0, 102, 204): GoTo cont 'vdd green
    If cnt = "v" Then rng.Characters(n, 1).Font.Color = RGB(204, 204, 255): GoTo cont 'dd grey
    If cnt = "w" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 128): GoTo cont 'vvd blue
    If cnt = "x" Then rng.Characters(n, 1).Font.Color = RGB(204, 153, 255): GoTo cont 'violet
    If cnt = "y" Then rng.Characters(n, 1).Font.Color = RGB(51, 102, 255): GoTo cont 'md blue
    If cnt = "z" Then rng.Characters(n, 1).Font.Color = RGB(102, 102, 153): GoTo cont 'greenish
    If cnt = "0" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 0): GoTo cont 'black
    If cnt = "1" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 0): GoTo cont 'green
    If cnt = "2" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 0): GoTo cont 'red
    If cnt = "3" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "4" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 255): GoTo cont 'l blue
    If cnt = "5" Then rng.Characters(n, 1).Font.Color = RGB(102, 0, 150): GoTo cont '???
    If cnt = "6" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 0): GoTo cont 'greenish
    If cnt = "7" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 128): GoTo cont 'd brown
    If cnt = "8" Then rng.Characters(n, 1).Font.Color = RGB(0, 128, 128): GoTo cont 'd green
    If cnt = "9" Then rng.Characters(n, 1).Font.Color = RGB(255, 153, 204): GoTo cont 'rouge
    If cnt Like "[A-Z]" Then
        rng.Characters(n, 1).Font.Color = RGB(255, 0, 255)  'dark red
    ElseIf cnt <> " " Then
        rng.Characters(n, 1).Font.Color = RGB(0, 0, 0)  'black
    End If
cont:
Next n
End Sub

 

COLORS in Cells: How to Get, Set and Use them

It's really easy to color Excel cells, their background, content or borders. We can do it either directly (using Format Cells... or some Font options) or indirectly - using Conditional Formatting in a variety of available ways in the Excel menu.

When it comes to determining (getting) exact codes/names of colors being already used in a worksheet, it can be a bit complicated. And it may happen that you need to know what colors were originally used for background filling of some cells. Well, we can't find it out by using any Excel function or formula. So, practically, we must use macros (VBA codes) to solve such puzzles. E.g., the following code will determine Color Index for background color of cell A2.

Press ALT+F11 keys to display the VBA editing window and enter there this code:

Sub getCellBG()

Dim getC as Integer

getC=Range("A2").Interior.ColorIndex

End Sub

How to create Activity Tracker / Timer

If you'd like to keep in Excel the record of your common daily tasks, in terms of disciplined use of your valuable time, then the solution I'm providing here can be helpful. It makes easy recording of duration of any routine daily activities and provides basis for further analysis of any kind.

This is how it looks like in an exemplary edition:

WeekDay and BirthDay

You know your birthday date, no problem. Do you know on which day of the week you were born? If not, you can find it out quite easily in Excel. There are many ways to do it, but probably the simplest one is to use the WEEKDAY function, as shown in this snip:

How to count any characters and digits in a text string using ARRAY formula

Let's say you need to find out how many characters, like e.g. a, or b, or w, or any other alphabetic character, or even digits, are there in a text string subject to your analysis.

Here is an example of a string located in cell A2:

Security guards at the hotel make sure guests remain in isolation, while police outside ensure no one enters the premises.

I want to count all characters (i.e. determine their frequency), from a to z (and A to Z) present within that string. This snip shows how easily it can be done using the ARRAY formula presented here: