Parsing out Questions and Answers Into Separate Columns

So here's a follow up to last night's tip further explaining how to put a series of questions and answers (or statements and responses as in a Rule 56.1 filing) into two different columns in an Excel spreadsheet.

Once you've identified the lines which show the beginning of statements by using the Regex search demonstrated in the Tip of the Night for August 25, 2016, the next step is make a control number column to the left.

1. Just insert a new column to the left, type 1 in the first cell and then select the rest of the cells in the column and go to Home . . . Fill . . . Series. Click off the Trend checkbox, make sure the Step Value is 1, and then click OK. You'll see that a list of sequential numbers is generated.

2. In column A we have the control number. In column B the text we're analyzing. Column C holds the results of the Regex search for the numbered lines, and in column D we have entered the beginning of the responses by, in this example, simply filtering on column B for Begins with . . . 'Response: '., or using =IF(LEFT(B4,10)="Response: ","Response","") .

3. Now filter on column C for just the numbered entries (check off Blanks) and enter 'STATEMENT' in column C. Type the entry in the first cell and then highlight the rest and press CTRL + D.

4. In column E enter this formula:

=IF(D2="STATEMENT","STATEMENT",IF(D2="RESPONSE","RESPONSE",E1))

Pull it down using CTRL + D and you'll see that it indicates which lines are statements (or questions), and which are responses (or answers).

5. Paste the data in columns C, D, and E as values, and then sort by column E and then by A.

6. In cell F2 enter this formula and pull it down for all of the question/statement lines:

=IF(D2="",F1&CHAR(10)&B2,B2)

You'll see that it's collecting the lines for each statement line by line and then resetting when it gets to the beginning of a new statement.

7. Now in column G we're simply going to tag the entries in column F which have the complete statements or responses by entering this formula:

=IF(OR(D3="Statement",D3="Response"),"tag","")

in G2 and pulling it down. Be sure to key in the tag for the last row.

9. Finally we simply paste the values in column F and G and then sort by column G and A. You can copy out the complied entries in column F, and the designations in column E which show what is a statement and what is response. Sort them and cut & paste to line them up.

Excel formula to reverse first name last name order

This formula is one that everyone should have in his or her toolbox:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)

When you have a name listed in cell A1 it will switch it to the Last name, First name format. So Ashton Eaton will become Eaton, Ashton.

So the way this formula works is as follows. This part:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

. . . finds text from the right in cell A1. The total length of the cell A1 is 12 characters (which we get by LEN(A1)) and then the formula subtracts from 12 the position of the first blank space in A1, which is at 7 [see FIND(" ",A1)]

So the RIGHT formula sees to take the 5 characters from the right of the cell.

&", "&

. . . adds in a comma and a space

LEFT(A1,FIND(" ",A1)-1)

The purpose of this part of the formula is to find the number of characters from the left of cell A1. The FIND formula again shows the position of the first blank space in the cell, which is subtracted by one to get the number of characters of the first name.

Excel Redaction / Cell Formatting Macro for Mouse Selected Area

PLEASE NOTE: USING THE REDACTION MACRO DEMONSTRATED IN THIS VIDEO WILL NOT NECESSARILY GUARANTEE THAT THE SELECTED TEXT CAN'T BE RECOVERED UNLESS THE SPREADSHEET IS CONVERTED TO AN IMAGE OR OTHER STEPS ARE TAKEN.

The purpose of the macro used in this demonstration posted to my YouTube channel is to allow a user to select a range of cells with his or her cursor and then have the macro automatically replace the selected range with a merged cell filled in black and captioned, 'Redacted'.

The general format of the macro can be used to apply any formatting information to a range selected by a user with a mouse.

1. To begin click on the View tab on the ribbon, and then select Macros . . . View Macros

2. Type in a name for the macro and then click 'Create'

3. Use this VBA code.

Sub redactrange() Dim Specifiedrange As Range Set Specifiedrange = Application.InputBox("Specifiy the range to be named: 'Table'", Type:=8) MsgBox "This is the range you selected " & Specifiedrange.Address Specifiedrange.MergeCells = True Specifiedrange.Font.Color = vbWhite Specifiedrange.Interior.ColorIndex = 1 Specifiedrange.Value = "Redacted"

Specifiedrange.VerticalAlignment = xlCenter End Sub

Thanks to jindon for posting the first part of the code at this forum: http://www.mrexcel.com/forum/excel-questions/235355-visual-basic-applications-code-select-range-using-mouse-hilighting-range.html

The first part of the VBA code lets you determine the 'Specifiedrange'. This 'Specifiedrange' is then referenced when setting formatting information in the second part of the code. You can add more formatting information at the end of the code by typing Specifiedrange followed by a command.

4. You can go back to View Macros to add a shortcut command to activate the macro.

5. Now when we press CTRL + j the macro will prompt us to select a range of text.

6. Click okay after entering your selection. A message will confirm the selected area, and another message will remind you that the values in the selected cells will be lost after they are merged.

7. We end up with a nice black box, captioned 'Redacted'.