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

Sun 06 Dec 2015

Solution - Challenge 29 – Reverse (Flip) a Number String

By |Sunday, December 06th, 2015|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 29 – Reverse (Flip) a Number String

You may use below formula to reverse a number string -

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

 

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

Sat 07 Nov 2015

Challenge 29 - Reverse (Flip) a Number String

By |Saturday, November 07th, 2015|Categories: Challenges|Tags: , , , , , , , |2 Comments

This time challenge is for flipping a number string. Excel VBA has got a function "StrReverse" which can be used to perform this task through VBA.

The challenge is to do this through a formula.

Let's say A1 = 70948

You formula should return a result of 84907

Note if you number string is 709480, your result will still be 84907 as leading zero doesn't have any meaning in a number string.

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 6-Dec-15.

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.

(more…)

Sat 11 Jul 2015

Tips & Tricks 99 – Remove Middle Name

By |Saturday, July 11th, 2015|Categories: Tips and Tricks|Tags: , , , , , , |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

1. Select your column.
2. CTRL+H
3. In Find What: box, put one space followed by one * followed by one space. Hence, you need to put " * " without quote.
4. In Replace With: box, put one space. Hence, you need to put " " without quote.
5. Press Replace All.
6. All Middle Names are removed.

B. Flash Fill (Only for Excel 2013) Way - If you data always contains 3 Words

1. Let's say that your first entry, Roberto Albert Carlos is in A1. Put Roberto Carlos in B1.
2. Same case do for A2 in B2 i.e. if A2 contains abc de fgh, put abc fgh in B2.
2. Select B3 and click Data tab > Flash Fill

Your column B will be filled in with Middle Name removed.

Sat 11 Jul 2015

Tips & Tricks 98 – Extract Middle Name

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

Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to extract the middle name. We have already talked about extracting first name and last name in following Tips and Tricks. We also talked about extracting initial of a middle name.

Tips & Tricks 22 – Extract First Name from Full Name

Tips & Tricks 23 – Extract Last Name from Full Name

Tips & Tricks 24 - Extract the Initial of Middle Name

Formula Way -

The formula for extracting Middle Name would be -

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

OR

=IF(COUNTIF(A1,"* * *"),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1)),2),FIND(" ",A1)+1,LEN(A1))),"")

OR

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

Manual Way -

A. Flash Fill (Only for Excel 2013) Way - If you data always contains 3 Words

1. Let's say that your first entry, Roberto Albert Carlos is in A1. Put Albert in B1.
2. Select B2 and click Data tab > Flash Fill

Your column B will be filled in with Middle Name.

B. Text to Columns Way - If you data always contains 3 Words

If your list of names is always having 3 names i.e. middle name is always present, we can have a manual way also.

1. Select your column.
2. Data tab > Text to Columns > Delimited should be selected in Step 1 of 3 and Next
3. Check Space in Step 2 of 3 and Next
4. In Step 3 of 3 - Select first column and check "Do not import column (skip)", Select third column and check "Do not import column (skip)" and Finish
5. You have only middle name in your column.

1

1

Sat 06 Jun 2015

Challenge 17 – Sum if Y Appears More than One Time Consecutively Maximum

By |Saturday, June 06th, 2015|Categories: Challenges|Tags: , , , , , , |2 Comments

The Excel file related to this problem can be downloaded from Challenge - Most Consecutive Appearance

Suppose you have a table like below. The challenge before you is to find the sum of prize money if Y appears consecutively more than once for a person maximum time. You need to input name in F2 and sum of prize money should appear in F3. If name doesn't meet the criterion of consecutive Y maximum times, then Prize money should be blanks or any message can be put here.

Taking the example of Smith, most consecutive times Y appears in rows 7 to 10. For Doe, rows 15 to 16 and for Liz 19 to 21. Note for Smith, Y appears consecutively 3 times, first time in rows 2 and 3, second time in rows 7 to 10 and third time in rows 12 to 13. But since, we are talking about maximum consecutive appearance, hence we need to consider row 7 to 10.

If their are ties, pick up the first maximum consecutive appearance.

Note - Without the use of a helper column, it may be very difficult to solve. Hence, please feel free to use a helper column. Maximum allowed helper column is 0.

1

Note - You may like to post your answer to this in comments.

Sat 18 Oct 2014

Tips & Tricks 60 - Remove numbers from string

By |Saturday, October 18th, 2014|Categories: Tips and Tricks|Tags: , , , , , |1 Comment

To remove numbers from a string (for example Vij1aY A. V4er7ma8 contains numbers which are not required), we can use nested SUBSTITUTE function to remove numbers. Use below formula assuming string is in A1 cell -

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A1,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Note - Since this formula is in multiple lines, hence you will have to copy this in Formula Bar. If you copy this formula in a cell, it will copy this in three rows.