Concatenate and Split Characters in Excel

One more post for Excel: combine or split characters.

Combine:
Suppose you want to combine the texts in cells A1, B1, C1 into cell D1.
===
In the target cell D1, type =A1&B1&C1. Enter and done. Pretty much like the overloaded "+" operation in many OOP programming languages.

Split:
Suppose in cell C3, we have text "12345", and we want to separate each character into one cell. For example, we can put "1" in cell D3, "2" in cell E3, and so on.
===
In cell D3, type =MID($\$$C3, (COLUMN(D3)-COLUMN($\$$C3)), 1), and press Enter.
Then use autofill to complete the rest of this row.

Select Every \(n^{th}\) Row in Excel

Today, I processed some Excel file and tried to select other row in a spreadsheet. Again, I am discussing the way with Excel, not programmatic.

Actually, my problem can be generalized to select every \(n^{th}\) row in the spreadsheet. The approach is based on Excel filter and described as below.

Excel version: 2010
Suppose the data area is C10:E20, and I am going to select every 3rd row.

1. In a separate area from the data matrix to be selected, locate a cell at the same row as the data area. Pick A10 in my example.
2. Type in the cell A10 =MOD((ROW(A10)-ROW(A$9)), 3), and press Enter.
3. Using autofill feature to complete all rows 10 to 20.
4. Locate Cell A10, click Filter under Data menu.
5. Select 0 only from the drop-down list.
6. Copy the filtered data into another separate area or a new sheet.
7. If copied in the same sheet, clear Filter to see complete data.

Create VBA Modules in Excel

Very often that people want to make some operations not supported by Excel itself. Yes, some friends asked me about this, and I also jumped into such situation sometimes. From a perspective of programmer, all operations like this can be done with some programming work. For general Excel users, they may not like programming and like some interactive way instead. Moreover, it's more straightforward to read and write files if working with Excel.

Here, I summarize a (hopefully) simple procedure to add customized VBA modules in Excel. Just for those who are interested.

1. Show "Developer" menu in Excel Ribbon

File → Options → Customize Ribbon, check "Developer" and click OK

2. Open a blank Excel workbook, and save as .xlam format in the default directory

For Office 2010 on Windows 7, the default directory is
C:\Users\[username]\AppData\Roaming\Microsoft\AddIns

3. Make the add-in available in Excel

Developer → Add-Ins, check or browse to locate the add-in you just created

4. Open VBA editor

Developer → Visual Basic, or ALT + F11, and then select proper project

5. Insert and edit customized module

In VBA editor, Insert → Module, then edit or copy VBA code

6. Save and exit

If you want to add the add-in to Excel Ribbon, you need create some XML markup first. I prefer not to do so, because I don't want to spend time doing that.

To use the add-in, after opening the data file on which you want to make operations, open VBA editor as above, then select the proper module and click Run Macro (F5).

I attach a sample VBA module code to delete the lower triangular entries of a matrix.


Reference:

Microsoft MSDN Library

Filter Text Values in Excel

Sometimes, friends ask me how to extract or get rid of some rows from an Excel spreadsheet. I did something similar before but also need some research to figure out the right way. Of course we can use a programming language and regular expression to do this task, but Excel has already provided such functions. Here I am posting to make a memorandum in case of future use.

In my current version Office 2010 (and I believe it's also the case for Office 2007), Excel provides a way to filter data based on customized criteria. Just follow "Data"->"Filter"->"Advanced". For numerical criteria, users can use relational operations in the criteria cell. This posts focuses on text values instead, and there are many options as listed below.

Notes:
The column names should exist and match for data area and criteria area.
Multiple criteria fields can be placed in adjacent columns.
Criteria in the same row means logical AND, and in different rows means logical OR.
* and ? are wild cards for this use and different from their functions in regular expression.
More combinations are possible based on the rules given below.

="=text"	Select cells whose contents are exactly equal to the string "text"
<>text	Select cells whose contents are not equal to the string "text"
text	Select cells whose contents begin with the string "text"
>text	Select cells whose contents are ordered (alphabetically) after the string "text"
*text*	Select cells whose contents contain the string "text"
text*text	Select cells whose contents begin with the string "text" AND contain a second occurrance of the string "text"
="=text*text"	Select cells whose contents begin with the string "text" AND end with the string "text"
?text	Select cells whose contents begin with any single character, followed by the string "text"
="=text?text"	Select cells whose contents begin with the string "text" AND end with the string "text" AND contain exactly one character between these two strings
="=???"	Select cells whose contents contain exactly 3 characters

References:

ExcelFunctions.net

Microsoft Office website