Sat 15 Jul 2017

## Challenge 64 - Sum up the Range where a particular alphabet appears

By |Saturday, July 15th, 2017|Categories: Challenges||1 Comment

Suppose, you have been given a range like this and you need to find the sum of column B where the alphabet "c" appears alone. The file related to this challenge can be downloaded from Challenge 64 - Sum up the Range where a particular alphabet appears

The answer to the above solution will be presented after a month i.e. on 15-Aug-17.

Sat 21 Jan 2017

## Tips & Tricks 154 - Insert a Space after Each Character

By |Saturday, January 21st, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro. (Flash Fill also works in most of the cases. But Flash Fill will copy in a different range which you will have to copy back and if data is scattered throughout the sheet, it may not be convenient)

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given and change "Sheet1" as per your requirement.
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

```' *** Macro by Vijay A Verma (eforexcel.com) *****
Sub InsertSpace()
Dim Ws As Worksheet
Dim Rng As Range, Cell As Range
Dim i As Long
Dim Str As String
Set Ws = Worksheets("Sheet1")
On Error Resume Next
Set Rng = Ws.Cells.SpecialCells(xlConstants)
On Error GoTo 0
If Rng Is Nothing Then Exit Sub
For Each Cell In Rng
Str = ""
For i = 1 To Len(Cell)
Str = Str & " " & Mid(Cell, i, 1)
Next i
Cell = Trim(Str)
Next Cell
End Sub```

Sat 14 Nov 2015

## Tips & Tricks 117 - VBA - How to Count a particular character in a String

By |Saturday, November 14th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , |0 Comments

When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string -

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))

In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be 5.

To count the same in a range of cells -

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"a","")))

Now, how to do the same thing in VBA. In VBA, you can use following expression to count this -

UBound(Split(LCase(Range("A1")), "a"))

To count the same in a range of cells -

UBound(Split(LCase(Join(WorksheetFunction.Transpose(Range("A1:A10")))), "a"))

Sun 19 Jul 2015

## Article 18 - LTRIM and RTRIM through Excel Formulas

By |Sunday, July 19th, 2015|Categories: Articles|Tags: , , , , , , , , |0 Comments

So, we have TRIM function in Excel. Almost all programming languages provide LTRIM and RTRIM functions also but Excel doesn't provide LTRIM and RTRIM. The same is provided in VBA but most of the Excel users are not using VBA. They are simple folks who want to accomplish their day to day job through Excel functions only.

Before I delve into LTRIM and RTRIM, let's recap TRIM.

TRIM, basically, removes all spaces from your string and if there are more than one space between your words, it will convert them into only one space. So, it removes all leading spaces, all trailing spaces and all spaces between words except one. If there is only one space between words, it will leave that space untouched.

Sat 11 Jul 2015

## Tips & Tricks 99 – Remove Middle Name

By |Saturday, July 11th, 2015|Categories: Tips and Tricks||0 Comments

Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to remove the middle name. Hence, you want to have an answer Roberto Carlos here.

Formula Way -

The formula for the above case would be

=IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"")

OR

=IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1),""),"")

Manual Way -

A. Easy Way