Author: Zeynep

Excel is an excellent application for mathematical operations, but sometimes you just need to organize data in tables that have text labels in every other row. We will walk through different methods to complete this task.

For this tutorial, we will use a data set of “Name”, “Date”, and “Salary”.

Using Visual Basic for Applications Code to Insert the Same Text in Every Other Row

You must enable the Developer Tab in Excel before inserting the VBA code into the worksheet.

1. Tap on File and click on Options toward the bottom of the menu.

2. Click Customize Ribbon on the left-hand side.

3. Place a check in the box next to Developer in the Main Tabs Category and press the OK button.

4. The Developer Tab appears in the Ribbon.

5. Tap Visual Basic in the Developer menu to open the Visual Basic for Applications dialog box.

6. Tap the Insert menu and select Module.

7. In the blank Module window, paste the following code and press Run.

Sub inserttexteveryonerow()
Dim Last As Integer
Dim emptyRow As Integer
Last=Range(“A” & Rows.Count).End(xlUp).Row
For emptyRow=Last to 2 Step -1
If Not Cells(emptyRow,1).Value =”” Then
Rows(emptyRow).Resize(1).Insert
Range(Cells(emptyRow,”A”), Cells(emptyRow, “C”)).Value=Array(“Name”, “Start Date”, “Salary”)
End If
Next emptyRow
End Sub

Important Notes:

• The data in your worksheet must start in cell A1
• Change the text surrounded by quotations of the Array to whatever you want them to be.
• The letters “A” and “C” represent the range of cells where the text appears.

8. The text appears in every other row of the worksheet.

Use Keyboard Shortcuts to Insert the Same Text in Every Other Row in Excel

Using keyboard shortcuts is a quick and easy technique to insert the same text in every other row in Excel; however, keyboard shortcuts are time-consuming when dealing with large amounts of data.

1. Create blank rows for the new text.

2. Select all the blank cells in the column.
3. Press F2 on your keyboard to turn on Editing Mode in the last cell of the group. You will see the cursor flashing in the cell.

4. Type the text you want in the cell.

5. Press CTRL + Enter, and the text appears in the remaining blank cells.

6. Repeat these steps for any remaining columns in your worksheet.

Then you can remove the text before or after a character (if present) after.

Use the Fill Handle to Insert the Same Text in Every Other Row in Excel

The Fill Handle in Excel works by copying a pattern that you create and it works to insert the same text in every other row in a worksheet.

1. Create your text labels in the worksheet and highlight the row with the labels and the blank row beneath it.

2. Place the mouse over the square in the bottom right-hand corner of the selected area, so the cursor turns into a black cross.

3. Press the Fill Handle box and drag it down through the rows until you have the desired number of labels.

4. Now you can copy the data under the labels.

Conclusion

Now you have a couple of quick, easy ways to insert the same text in every other row of your worksheet. We encourage you to continue learning more Excel tricks from our blog.

This includes adding alternate row colors to make the entries pop out more.

Indenting text in Excel increases the readability of lists and assists with breaking large amounts of data into sections, making it easier for you to decipher. Excel provides a few methods for indenting text.

Let’s delve into the Excel world of indenting text!

Using the Tools in the Alignment Group to Indent in Excel

1. Highlight the cell(s) that you want to indent.

2. Click the Increase Indent button in the Alignment Group.

3. The list of apples is indented in the cell.

Using the Format Cells Dialog Box to Indent in Excel

1. Select the cell(s) that you want to indent.

2. Click the Dialog Box Launcher in the Alignment Group.

3. The Format Cells Dialog Box appears.

4. The Format Cells Dialog Box has many options for indenting text, including Left, Right, or Distributed, and you control the amount of indentation by increasing the Indent options, which go up to a maximum of 250.

5. The following example shows the three types of indentation as well as one group of cells with no indent applied.

You can then copy the formatting of these cells to not do it again manually after pasting.

Using Keyboard Shortcuts to Indent in Excel

1. Select the cells that you want indented.

2. Press ALT + H + 6 on the keyboard, one key after the other to indent the text by one space. You can repeatedly use this shortcut if you choose to indent your text more than one space.

Multiple Lines of Text to Indent in Excel

Note: The following steps might not work correctly if you are using an Excel version older than Excel 365.

1. Highlight the cells or the range of cells.

2. Press the Increase Indent button in the Alignment Group to indent the text.

Conclusion

This short tutorial provided a few techniques for indenting text in Excel. Now, formatting individual cells or even multiple lines of text in one cell is a breeze.

Excel is not just for mathematical calculations, the software also offers many powerful ways to manipulate data, including separating text strings. Manipulating text is accomplished through several methods and we walk through five options in this tutorial.

Use the Find and Replace Function to Remove Text Before or After a Specific Character in Excel

 For this tutorial, we will work with employee names and company roles separated by semicolons in individual cells in Excel.

1. Select all the cells in your worksheet.

2. Tap the down arrow to the right of Find & Select in the Editing Group on the Home Tab and then click Replace…

3. Type an asterisk (*) followed by a semicolon (;) in the Find What: portion. Leave the Replace text box empty and tap the Replace all button.

4. Excel deletes the employee names from the cells, leaving just the company roles.

Use Find and Replace to Remove Text After a Specific Character in Excel (H3)

1. Select all the cells in your worksheet.

2. Click the down arrow to the right of Find & Select in the Editing Group on the Home Tab and then tap Replace…

3. Insert a semicolon (;) accompanied by an asterisk (*) in the Find What: portion of the dialog box. Leave the Replace text box empty and press the Replace all button.

4. The company roles are deleted and only the employee names remain.

Use Formulas to Remove Text Before or After a Specific Character in Excel

Functions in Excel require arguments for them to work properly.

The TEXTBEFORE function has four arguments:

• Text: The text you are searching for. This is required.
• Delimiter: The character that marks the exact place in the text before the text you want to remove. This argument is required. This is also used to separate first and last names.
• Instance_Num: The number of instances in which the delimiter appears after the text you wish to extract. This argument is optional, but the default is equal to 1.
• Match_Mode: Can be used if the text is case-sensitive. This argument is optional, although if you choose to use it, 1=case-insensitive, 0=case-sensitive. 
• Match_End: Sees the end of the text string as a delimiter. This argument is also optional; however, you can enter 0 to not match the end of the string or 1 to match the end of the string.

Now, let’s use the TEXTBEFORE function in our example.

1. Place a new column next to your original data.

2. Type the following into the first cell of the new column: =TEXTBEFORE(A3,”;”,1,1,0).

We used some of the optional arguments in the formula so that you can see them in action.

• A3 is the cell where the search text is
• The semicolon is the delimiter and must be enclosed in quotation marks
• The first number 1 is the first and only instance of the delimiter
• The second number 1 tells Excel that the match_mode is not case sensitive
• The zero signals Excel to not match the delimiter at the end of the text.

3. Use the Fill Handle in the bottom right-handle corner of the cell to copy the formula through the remaining cells.

Then you can save the data afterwards without using formulas.

Use the TEXTAFTER Function to Remove Text After a Specific Character in Excel

The TEXTAFTER function has the same arguments as the TEXTBEFORE function, except for the delimiter marks the exact position in the text string after the text you want to extract. 

Let’s apply the TEXTAFTER Function to our example data.

1. Insert a new column beside your original dataset.

2. Type the following formula into the first cell of the new column. =TEXTAFTER(A3,”;”,1,0,0).

3. Use the Fill Handle to insert the formula into the remaining cells.

Use Flash Fill to Remove Text Before or After a Specific Character in Excel

Flash Fill recognizes a pattern of data and will automatically fill a column with new data based on that pattern.

1. Insert a new column next to your data.

2. Type the text that you want to appear in the first cell of the new column and press the ENTER key.

2. Start typing the data into the next cell. You see a dim list appear with the pattern for the existing data.

3. Press the ENTER key and Excel will finish the pattern for you.

Conclusion

Now, you have learned different ways of extracting data (and maybe URLs from hyperlinks, too). Also you can check other related tutorial that explains How to Remove a Specific Character from a String.

Excel can handle many complex calculations and formulas to assist you with datasets. There are times when you need to use Excel to open files that are not native to the Excel application.

This tutorial will walk you through the steps necessary to open your .dbf file in Excel.

Open a DBF File in Excel Using the Open File Command

1. Open Excel and navigate to the .dbf file. Excel will only show native files, so you must change the file type in the dialog box from “Excel Files” to dBase files.

2. Click the .dbf file in the dialog box and tap the “Open” button.

3. The file opens in Excel and you can edit it to fit your needs.

Open a DBF File in Excel Using the Get Data Command

1. Open Excel and tap “Data” in the top menu.

2. Click the down arrow next to Get Data to expand the options, select “From Other Sources…” and click “From Microsoft Query.”

3. Tap the “Options…” button in the “Choose Data Source” dialog box.

4. Position the mouse pointer directly after .oqy in the “File Name:” section and type: “ ;*.dbf.” Next, in the “Folders:” section, navigate to the drive where the DBF file exists and click the “OK” button.

5. Click the “OK” button in the “Data Source Options” dialog box.

6. Click the “OK” button in the “Choose Data Source” dialog box, and the “Create New Data Source” dialog box appears. Input a filename in the “What name do you want to give your data source?” box, and then choose “Microsoft Access dBase Driver” from the drop-down list under “Select a driver for the type of database you want to access:”

7. Click the “Connect…” button and the ODBC dBase Setup box appears. Leave the version as dBase 5.0 and tap the “OK” button.

8. The file name path to the DBF file appears next to the “Connect…” button. Click the “OK” button.

9. You will see the filename of your DBF file highlighted in the “Databases” tab. Click the “OK” button in the “Choose Data Source” dialog box.

10. The tables and columns appear on the left-hand side of the “Choose Columns” dialog box. Highlight the tables and columns to include in your query, and then click the chevron arrow to move them into the “Columns in your query” portion of the dialog box. Click the “Next” button.

11. The query wizard allows you to filter the data if you choose. For this tutorial, we will not be filtering data. Tap the “Next” button on the wizard.

12. The “Sort Order” dialog box appears. You can select how to sort the data from your table. Once you have made sorting selections, tap the “Next” button.

13. Make sure that the “Return data to Excel” radial button is selected, and tap the “Finish” button to complete the query.

14. Choose how to import the data into Excel in the “Import Data” dialog box. For this tutorial, we will import the data as a simple table. Click “OK” to finish the import.

15. The data from the DBF file is displayed in the worksheet.

Conclusion

Now you have learned two different methods for opening a .dbf file so that you can manipulate the data in Excel. Please feel free to take a look at other tutorials on our website.

Sorting information in Excel helps with classifying your data and arranging it in ascending or descending patterns. This tutorial walks you through several ways of sorting the data, including sorting statically or using formulas to keep the datasets dynamic.

Using the Sort Function to Sort by Last Name in Excel 

1. Highlight the table that you wish to Sort.

2. Click the down arrow in the Sort and Filter Group, and then choose “Custom Sort…“

3. The Sort dialog box opens, click the down arrow next to Sort By, and click “Last Name.”

4. Next, click the “Add Level” button and select “First Name.”

5. Continue adding levels for each data type in the table, so your Sort By dialog box looks similar to this.

Sometimes the data used in a sort is not separated into its own column, so you need to extract the data and place it into another column before sorting it.

Using Text to Columns to Sort by Last Name in Excel

1. Create a new column for the Last Name field.

2. Highlight the Name column.

3. Click the Data tab and then select “Text to Columns” in the Data Tools Group.

4. The Text to Columns dialog box appears.

5. Choose whether your data is “Fixed Width” or “Delimited.” Make sure you are happy with the preview of your data and then click the “Next” button.

6. Click the checkbox next to “Space,” to let Excel know that a space separates the first name from the last name. Make sure the data preview shows a vertical line between the first and last names and click the “Next” button.

7. The next screen allows you to set the formatting for your dataset.  Given that we are working with text, leave the option selected as “General” and click the “Finish” button to complete the Text to Columns Wizard.

Note: If Excel returns a message that data already exists in the original Name column, click the “OK” button to copy over it.

Using the Find and Select Function to Sort by Last Name in Excel

1. Insert an empty column next to the names in your data and copy the Names into it so that you have two columns with the same data.

2. Select all of the data in the second column and then click on Find & Select in the Editing group of the Excel toolbar and click “Replace” to open the dialog box.

3. Type an asterisk (*) and one blank space in the “Find what:” field and leave the “Replace” field blank.

4. Tap the “Replace All” button and then close the dialog box.

Note: Using the Find and replace option in Excel is fast and easy but the only problem is that it leaves the data static. When you add names to the list, you must go back and perform Find and Replace for each new name you add to the column. So, implementing a formula into the Last Name column will save you time because you will only have to copy the formula to each new addition.

Use the LEN, RIGHT, and SEARCH Functions to Sort by Last Name in Excel

The RIGHT and LEN Functions are used to count the characters in the text string while the SEARCH function finds a specific place in a string of text, which happens to be a space in our example.

1. Insert a new column next to the column of names in your table.

2. Type the following formula in the first cell of the new column: =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))

3. Copy the formula from cell B2 through the remaining cells in the column using the Fill Handle, and your new column will look similar to the image below.

Using the Flash Fill Method to Sort by Last Name in Excel

The Flash Fill concept in Excel is programmed to recognize patterns in your data and predict the patterns and fill them in without much effort from you. Here is how the Flash Fill method works in Excel.

1. Create an empty column for the Last Names in Excel.

2. Type the Last Name of the first person in the dataset and then press the <ENTER> key.

3. Start typing the Last Name of the second person in the list and a list will appear for the remainder of the data set.

4. Press the <ENTER> key again and the last names appear in the column.

Using Power Query to Sort by Last Name in Excel

1. Click any cell in your data.
2. Click the “From Table” icon in the Get & Transform Group.

2. The Create Table dialog box appears. Make sure that you select the entire table and press the “OK” button.

Note: If Excel returns a Security Message, press “OK” to accept it.

4. The Power Query Editor opens.

5. Click the down arrow next to “Split Columns,” and tap the “By Delimiter” option.

6. The Split by Delimiter dialog box opens. Select “Space” as the delimiter and then select “Right-most delimiter” in the “Split at” section and then press the “OK” button.

7. The Power Query splits the Last Name from the First Name and places the Last Names in a new column.

8. Click “Close & Load” in the upper left-hand corner of the Power Query dialog box.

9. The dialog box closes and the new table appears in the Excel sheet.

10. You can rename the columns previously changed by the Power Query. Click the Name.1 cell and insert a new name in the Formula Bar. Repeat the process for the Name.2 cell. We will choose “First Name” and “Last Name” for our example.

Now that you have built a Power Query, the next time you need to separate First Names from Last Names, you can just connect your power query to the file and run the query to complete the process.

Conclusion

Hopefully, this tutorial taught you some different ways to separate and sort data and inspires you to continue learning the different aspects of working with data in Excel.