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 ( *****
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


Tue 07 Jun 2016

Solution - Challenge 42 – Split a Sentence Into Words in Different Cells

By |Tuesday, June 07th, 2016|Categories: Solutions|Tags: , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 42 – Split a Sentence Into Words in Different Cells

Enter following formula in B1 and drag to the right and down.

=IFERROR(MID($A1,FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A))),
FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:B)))-1-
FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A)))),"")

The workbook containing this solution can be downloaded from Solution - Challenge 42 – Split a Sentence Into Words in Different Cells

Sat 07 May 2016

Challenge 42 - Split a Sentence Into Words in Different Cells

By |Saturday, May 07th, 2016|Categories: Challenges|Tags: , , , , , , , , |0 Comments

This time the challenge before you is to write a formula that if a sentence is given in A1, your formula if dragged to the right should split the sentence into words.

Remember, it has to be a single formula which should be put into B1 and dragged to the right.

See the examples below -


A workbook containing this challenge can be downloaded from Challenge 42 - Split a Sentence Into Words in Different Cells

The solution to above challenge will be published on 7-Jun-16.

Sat 12 Sep 2015

Article 25 - Reverse FIND / SEARCH & MID Function

By |Saturday, September 12th, 2015|Categories: Articles|Tags: , , , , , , |0 Comments

Excel doesn't offer any function for reverse FIND / SEARCH & MID function. VBA does offer a function INSTRREV but this article is for non-VBA folks or folks who do not want to use VBA in their spreadsheet.

Hence, the only option before us is to build them through formulas. Let's build a reverse FIND / SEARCH function.

Note - There are two differences between FIND and SEARCH which you must make a note of.


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.