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.

 

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 -

1

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 30 May 2015

Tips & Tricks 93 - Sorting from Left to Right

By |Saturday, May 30th, 2015|Categories: Tips and Tricks|Tags: , , |0 Comments

Generally, we do sorting in a column from top to bottom. But, what happens if the data is in rows. Then you will have to sort from left to right. This is possible in Excel.

1. Select your data in the row.
2. Data tab> Sort
OR
Home tab > Sort & Filter
OR
ALT+DS

11

3. You will get following screen. Here, you may choose appropriate action.

11

4. Pressing OK brings us to Sort screen. There, you may choose Options to bring up Sort Options. Here, you can choose Sort left to right to affect sort.

11