CBA Sage (Peachtree) Accounting Support Logo



Linkedin Rss Gmail
Shopping Cart empty ( 0 )
Go Shopping!

Buy Sage 50 2018 Sage Certified Master Consultant logo


Welcome to the Computerized Business Blog please feel free to look around.


How to Remove Blank Cells in Your Excel Spreadsheet

14

This easy Excel tip will show you how to delete a number of cells and then shift all the cells so that there are no blank cells without accidentally deleting any spreadsheet data.   (This applies Microsoft® Excel® for Windows 2010, 2013, and 2016.)

As you can see above, partial data has been removed leaving blank spaces in between the data.

To delete the blank cells and move all other data, follow these steps:

1. Highlight the cells with the data and blank cells in the worksheet.

2. On the Home Ribbon, under the Editing tab, click on Find and Select, and then Go To Special from the drop-down list.

3. Select Blanks and click OK.

Note that Excel now highlights all the blank cells.

4. Right click on any of the highlighted cells and select Delete.

5. Select Shift Cells Up and click OK.


Excel will now delete all the blank cells and the data order is maintained. 


By

How To Easily Hide Zero Values in Excel

16

In order to have neat looking reports in Microsoft® Excel®, you may choose to hide all zero values. For this Excel tip we show how to hide zero values for selected cells by using the custom number format. The hidden zero values appear in the formula bar when a cell is selected. Values that change to non-zero digits will be displayed in the cell range.

This tip applies To: Microsoft® Excel® 2013 and 2016.

  1. Select a data range 
  2. Then press Ctrl+1 and select the Number tab then Custom.
  3. In the Type box, enter 0;;;@ then select OK.

To display hidden values:

  1. Select a data range
  2. Then press Ctrl+1.
  3. Select the Number tab then General.
  4. Select OK.

The cells with zero values will display blank cells. This will give the report a neat look and make it more presentable. 


By

Remove Hyperlinks from an Excel Worksheet

18

Below is a quick and easy way to remove hyperlinks from an Excel worksheet. This is done by applying a code to your worksheets that will save you time and avoid having to manually remove the hyperlinks.

This simple tip will remove all the hyperlinks from your Excel worksheet in one click! 

(Applies To: Microsoft® Excel® 2007, 2010, 2013, 2016)

1. Ensure you are on the worksheet where you want to remove the hyperlinks.

2. Press ALT+F11 to open the Visual Basic Editor.

3. From the Menu bar, select Insert Module and copy and paste the codes below into the window on the right.

Sub RemoveHyperlinks()

Activesheet.Hyperlinks.Delete

End Sub

4. From the Menu bar, select File, Close and Return to MS Excel.

5. Press ALT + F8 to view a list of Macros.

6. Select the Macro, RemoveHyperlinks, and then select Run.

(Make sure that your Macro Settings are not disabled under the Macro Settings Option.)

To set your Macro security level, follow the steps below:

  • On the Office/File tab, select Options.
  • On the left side of the dialogue box,select Trust Center.
  • Select the Trust Center Settings button.
  • On the left side, select Macro Settings.
  • Select Disable all macros with notification.

All the hyperlinks have now been removed.  


By

Use the Excel MROUND Function to Round Off Time Values

16

This Excel tip is a great tool if you work with units of time values, especially HR personnel, IT security professionals, stock controllers etc.

(Applies To: Microsoft® Excel®2010 and 2013)

1.  Start by formatting numbers to the time format. (See image in #3 below.)

  • Highlight the range B3:B9.
  • Right click and select format cells.
  • Select custom under the number tab.
  • Select h:mm:ss then select OK.

2.  To round off to the nearest hour.

  • Place the cursor in cell C3.
  • Type =MROUND(B3,1:00″).
  • Press enter and copy the formula down.

3.  To round off to the nearest minute.

  • Place the cursor in cell D3.
  • Type =MROUND(B3,0:01″).
  • Press enter and copy the formula down.

The time values have been rounded to the nearest hour or minute. Only minutes or seconds that are greater than or equal to 30 are rounded off.


By

How to Rearrange Data Table Columns in Excel

5

As with most commands in Microsoft® Excel®, theres an easy way and a long way to rearrange columns in a data table. The long way would be to highlight a column, cut it, and then paste it where you want to move it to. Here's a quicker way to do this.  (Applies To: Microsoft® Excel® 2010, 2013 and 2016.)

1.Below is a two column data table.

Picture 1

2. If you wanted to move the Amount column before the Sales Rep column,  you would simply select the column header, then press ctrl + shiftdown arrow.

Picture 2

3. Then, click and hold the green outline of your range (you must see a 4 arrow cross before you click and hold).

Picture 3

4. Finally, drag your column to the left and release the click.

And thats it! This is an easy yet effective method to rearrange columns in a data table, saving you time and improving the way you work in Excel.


By

How to Calculate a Deposit or Down Payment in Excel

12

Purchasing a car or a house is usually a pleasant experience for the buyer. However, most financial institutions require a deposit or down payment to be made towards the total purchase price.

In this tip, we show how to calculate the deposit or down payment for a car. For instance, the cost of the car could be $15,000 to be repaid over four years at 3% per period. Furthermore, you may want to keep the monthly payment at $400 per month, but need to calculate the deposit. This is how it can be done.

Applies To: Microsoft® Excel® 2010 and 2013

1. We are going to use the following formula:

=Purchase Price-PV(Rate,Nper,-Pmt)

  • PV: calculates the loan amount.
  • The loan amount will be subtracted from the purchase price to get the deposit amount.
  • Rate: is the interest rate per period. It will be divided by 4 if it is per quarter or 12 if its per annum.
  • Nper: is the total number of payment periods in an investment, which will be 48(4*12).
  • Pmt: is the payment made each period.

2. Place the cursor in cell C6 and enter the formula below.

=C2-PV(C3/12,C4,-C5)

3. This will give you $3,071.48 as the deposit.

1. Excel On Steroids_CalculateDepositsorDownPayments

By using this method you can calculate the deposit for a car or house using an easy formula.


By

Easily Generate PivotTables on Separate Excel Worksheets

10

Post Last Updated: 5/10/2016 10:35:09 AM 

You have taken time to create a product sales PivotTable and now you would like to analyze the data. You want to analyze the sales per branch by quickly generating PivotTables for each branch to be placed on separate worksheets.

In this tip, we take you through a few easy steps as we show you how this process can be easily automated.

Applies To: Microsoft® Excel® 2007,2010,2013,2016.

1. Open the practice workbook and select any cell within the data list.

2. Select InsertPivotTable and then select OK.

3. An empty PivotTable will open on a new worksheet. Refer to the PivotTable field list and place the fields as follows.

a. Branch: Filters Area.

b. Product Name: Rows Area.

c. Product Sales: Values Area.

1_ExcelOnSteroids_PivotTables

4. Your PivotTable will now appear as follows:

2_ExcelOnSteroids_PivotTables Note: Branch in the Filters Area.

5. You now want to display the Branch details on individual worksheets:

a. Go to the Analyze Tab, select the drop down arrow next to Options, and select Show Report Filter Pages and select OK.

3_ExcelOnSteroids_PivotTables

6. The individual details will be displayed on separate worksheets.

Your sales data for the different branches will be extracted and placed on separate worksheets, thus making it easier to view and analyze your sales data at a glance, which allows for better decision making.


By

Learn Some New Features In Excel 2013

28

Post Last Updated: 5/2/2016 11:08:54 AM 
May is all about Microsoft Excel!  In this first part of a 4-part webcast series highlighting the power of Excel, register below to learn more about the  exciting features of Excel 2013, including flashfill, slicers, sparklines, automatic tables, etc. 

 

Register
Date and time: Thursday, May 5, 2016 2:00 pm 
Eastern Daylight Time (New York) 

  Thursday, May 5, 2016 12:00 pm 
Mountain Daylight Time (Denver)
  Thursday, May 5, 2016 1:00 pm
Central Daylight Time (Chicago)
  Thursday, May 5, 2016 11:00 am
Pacific Daylight Time (San Francisco)
Duration: 45 minutes



By

Create an Excel Combination Chart From Scratch

26

After you have created your Microsoft® Excel® spreadsheet, you can visually represent the worksheet data by creating a chart. Charts often make your data clearer and easier to understand.

If you find that the data is not in sequence or in a group of cells, you may need to create your own series, specifying the labels and values. Data can be extracted from different worksheets and even workbooks.

TIP: To create a quick chart, select any cell within the data range and press F11.

In this tip, we are going to create a combination chart from scratch showing the difference between the Sales and Purchases.

Applies To: Microsoft® Excel® 2007,2010 and 2013

  1. Open the practice workbook.
  2. Create a blank line chart:

a. Select any blank cell on the worksheet.
b. From the Insert tab, in the Charts group, select Line.
c. Select 2-D Line.

combinationChartScratch1

3. Add data to the chart:

a. Select the blank chart.
b. From the Design tab, in the Data group, select Select Data.

combinationChartScratch2

  1. To create the Sales series:
    a. Select the Add button in the Legend Entries (Series)
    i. Click in the Series name text box and select cell A4(Series name).
    ii. To highlight the data range, click in the Series values text box.
    iii. Clear the contents and select the range C5:C16.b. The Edit Series box should contain the following:

combinationChartScratch3

c. Select OK.

  1. To create the Purchases series:
    a. Select Add.
    i. Click in the Series name text box and select cell A21(Series name).
    ii. To highlight the data range, click in the Series values text box.
    iii. Clear the contents and select the range C22:C33.b. The Edit Series box should contain the following:

combinationChartScratch4
c. Select OK.

  1. Select the Edit button in the Horizontal (Category) Axis Labels.
    a. To display the months on the horizontal line, highlight the data range A5:A16 or A22:A33.
    b. Select OK.

7. Add a chart title:
a. Select the chart as below.

combinationChartScratch5
b. Enter the heading as Sales Vs Purchases.

8. Move the chart to a new worksheet:
a. From the Design tab, in the Location group, select Move Chart.
b. Select New Sheet and name the worksheet as shown below.

combinationChartScratch6
c. Select OK.

9. Right click on the Sales series.
10. Select change series chart type.
11. Select the down arrow under chart type at the bottom of the window.
12. Select Clustered column and select OK.

combinationChartScratch7

You can place charts in different worksheets to make it easier for you to analyse your information. The chart that we have created compares Purchases to Sales, which can quickly help you with decision making.


By

Learn How to Use the Goal Seek Feature

17

If you know the result you want from a formula, but you are unsure of what input value the formula requires to get that result, you can use the Goal Seek Feature in Microsoft® Excel®. Goal Seek takes a result and determines possible input values that produce the result you are looking for. Data analysis can be enhanced with the use of Excels What-If analysis.

You can use the Goal Seek to get a preferred result by adjusting one of your input values. Lets say you own a hotel and would like to know the number of rooms to rent out in order to make a profit of $20,000. You can use Goal Seek to achieve the result by adjusting the rooms to rent out variable.

Applies To: Microsoft® Excel® 2013 and 2016

Apply these easy steps to use Excels Goal Seek feature:

1. Select the Goal Seek

2. Select cell B4. Note the formula used to calculate the required profit. (Rooms rented *Price per room )- Expenses.

3. Select What if Analysis under the Data tab and then Goal Seek.

4. In the Set cell box, select cell B4, the cell with the formula you want to resolve.

5. In the To value box, enter 20000.

6. Place the cursor in the By Changing box and select cell B5.

1. ExcelOnSteroids_GoalSeekFunction

7. Select OK twice. The result will be as below.

2. ExcelOnSteroids_GoalSeekFunction

As you can see the number of rooms to rent out were easily calculated without using a complex formula. It is important to remember that the Goal Seek option can only adjust one variable at a time. 


By

How to Calculate Multiple Results in Excel by Using a Data Table

24

Post Last Updated: 2/24/2016 9:00:23 AM 

When you examine business data in Microsoft® Excel®, you may want to discover the result of formulas if you provide different inputs. You can calculate the results of those changes by using a data table which makes it easy to examine a range of possibilities at a glance.

You can create one or two variable data tables, depending on the number of inputs and formulas that you want to test.

Use a one variable data table if:

  • You want to see how different values of one variable in one or more formulas will change the results of those formulas.

Use a two variable data table to:

  • See how different values of two variables in one formula will change the results of that formula.

In this example, we use a two variable data table to calculate the projected sales for 2016. The input values are possible sales and expense growth.

Applies To: Microsoft® Excel® 2010 and 2013.

1. For a two variable data table, the copy of the original formula must be entered at the intersection of the row and column input values. At cell B7, type =B5.

(The formula in B5 is =(Sales 2015+( Sales 2015*Growth 2016)-( Sales 2015*Expense 2016)).

2. Type one list of input values in the same column, below the formula.

3. Enter the second list in the same row as the formula, to its right.

1. ExcelOnSteroids_TipsAndTricks_WhatIfAnalysis

4. Select the range of cells that contains the formula, both the row and column of values, and the cells in which you want the calculated values. (B7:H15).

5. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.

6. In the Row input cell box, enter the reference to the input cell for the input values in the row.

7. In the Column input cell box, enter the reference to the input cell for the input values in the column.

2. ExcelOnSteroids_TipsAndTricks_WhatIfAnalysis

8. Click OK.

9. Format the values in the range C8:H15 to the currency of your choice.

3. ExcelOnSteroids_TipsAndTricks_WhatIfAnalysis

A table displaying the projected sales for 2016 has been generated. The table shows the projected sales for 2016 based on possible sales and expense rates.


By

How to Create a Waterfall Chart

22

Post Last Updated: 1/22/2016 7:33:19 AM 
A waterfall chart is a special type of column chart used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns.

Below are the steps to create a waterfall chart:

Applies To: Microsoft® Excel® 2010 and 2013

  1. We start by setting up the data. Select the Income and Expense Analysis worksheet.

tip-1

Note:

The value in E5 (Running Total) is Revenue (G4) Cost of Sales (F5).

The Running Total in E7 equals Gross Profit (G6)-Fixed and Semi-fixed Expense (F7).

The value in E8 is the Net Profit and equals Running Total (E7) variable Expenses (F8).

  1. While holding down the Ctrl key, select cells B3:B9 and cells E3:G9.
  2. Select the Insert tab.
  3. Select Column under the Charts group.
  4. Select the Stacked Column Chart.

tip-2

tip-3

  1. Right click on the Running Totals series on the chart.

tip-4

7. Select the Format Data Series menu item.

8. Select the Fill

9. Select the No fill radio button.

tip-5

  1. Select the Border Colour
  2. Select the No line radio button.
  3. With the Format Data Series window still open, select the series on your chart that reflect Income.
  4. Select the Fill
  5. Select the Solid fill radio button.
  6. Select the colour that suits the theme of your dashboard.
  7. With the Format Data Series window still open, select the series on your chart that reflect Expenses.
  8. Select the Fill
  9. Select the Solid fill radio button.
  10. Select the Red
  11. Click the Close

tip-6

  1. Right click on one of the Horizontal Major Gridlines and select

tip-7

  1. Right click the Vertical Value Axis and select Format Axis.

tip-8

23. From the Axis Options menu, select the Display Units drop down.

24. Select Millions

tip-9

  1. Select Close.
  2. Select the Waterfall Chart.
  3. Select the Layout tab then Chart title.
  4. Select above Chart.
  5. Enter Income and Expense Analysis.

tip-10

The Revenue, Gross Profit and Net Profit are whole columns and show positive values. The Cost of Sales, Fixed and Semi Fixed expenses and Variable expenses show negative values and are depicted as floating columns.


By

Excel Tip: How to Change Upper/Lower Case Text in Excel

10
Have you ever had to change your text from upper case to lower case (or vice versa) in Microsoft® Excel®? 

A new feature in Excel 2013 and 2016 is the Flash Fill option. This can be used to change the case of consistent text. It can also be used to split and join (concatenate) consistent text.  See the steps below:
  1. Select cell B2 and enter JANUARY.

flash-fill1

2. Select the Data tab and then Flash Fill.

flash-fill-2

3. The months will all be converted to upper case.

flash-fill-3

The months have been quickly and easily converted to upper case without the use of complex formulas.


By

Common Microsoft Excel Errors and How to Fix Them

19

Post Last Updated: 11/19/2015 8:15:19 AM 
Below are some common Microsoft Excel errors and how to fix them (applies to Microsoft® Excel® 2010 and 2013):

1. ###### error.

a. The column is not wide enough to display all the characters in a cell.

b. Extend the width of the column.

2. # Div/0! error.

a. Excel displays this error when a number is divided either by zero (0) or an empty cell.

b. Change the divider to a value that is not equal to 0.

3. #Name? error.

a. The text in a formula is not recognized by Excel.

b. This is caused by misspelling of function names.

c. For instance =su(A1:A7) will result in the #Name? error.

d. Correct the error by entering =sum(A1:A7).

4. #Value! error.

a. This error will be displayed if the formula includes cells that contain different data types.

b. = B1(85) + B2(75) +B3(data) will result in the #Value! error message.

c. Replace B3(data) with a numeric value and the error will be corrected.

5. #REF! error

a. The error will be displayed when a cell reference is not valid. Deleting cells that were referred by other formulas will cause this error.

b. Refer the cells to the correct ranges and the error will be corrected.

6. #NUM! error.

a. The formula or function contains invalid numeric values.

b. Using $ ,% symbols with the number can result in this error.

7. #NULL error.

a. Excel displays this error when you specify an intersection of two areas that do not intersect (cross).

b. The intersection operator is a space character that separates references in a formula.

c. =Sum(A1:A2 C3:C5) returns the #NULL error because the two ranges do not intersect.

d. =SUM(A1:F1 B1:B10) will return the correct reference.


By

Link an Excel Worksheet Name to a Cell Using the MID and FIND Functions

18

Post Last Updated: 11/18/2015 2:52:14 PM 

Do you prepare or present reports on a regular basis?  If you use  Microsoft® Excel® to present reports the report headings can contain worksheet names and you can link worksheet names to a cell. This can be achieved by using the MID and FIND functions in Excel. This will save you the time of re-typing the worksheet names in the report headings.

In the example below you can have your worksheet names for each month in a workbook and display the worksheet names in the report heading automatically.  (This tip applies to Microsoft® Excel® 2010 and 2013.)

1. Select cell C1. Then select the Formulas

2. Select Text and then MID from the text functions list.

1.ExcelOnSteroids_TipsAndTricks_LinkWorksheetNames

3. Place the cursor in the Text box and enter CELL(filename,A1).

The CELL function returns information about the formatting, location, or contents of a cell.

  • It has two arguments info_type and reference.
  • Info_type: text value that specifies the information type to be returned.
  • Reference: Is a cell that you want information about.
  • Filename will return the path of the filename.

C:\Excel Tips\[Linking a worksheet name to a cell.xlsm]Janaury

4. Place the cursor in the Start_num box and enter FIND(],CELL(filename,A1))+1.

  • The location within the file path name where to start extracting the worksheet name.
  • ] instructs Microsoft® Excel® to find the position for the square bracket.
  • +1 ,adds 1 to the position returned by searching for the square bracket.

5. Place the cursor in the Num_Chars box and enter 32, then select

  • 32 is the maximum number of characters for a worksheet name.

2.ExcelOnSteroids_TipsAndTricks_LinkWorksheetNames

The worksheet name will be linked to the cell automatically plus whenever the worksheet name is changed it will automatically update the cell it is linked to! 


By

Round off Time Values in Excel Using the MROUND function

12

Working with time values comes with its own challenges. One of the issues is how to round off time values from seconds to minutes or minutes to hours.

You can use the MROUND function to round off time values. The MROUND function returns a number rounded to the nearest instance of a specified multiple.

Itfs easier to manipulate time values if they are rounded off. So, if you work with time values you will find this tip useful, especially HR personnel, IT security professionals, stock controllers etc.

Applies To: Microsoft® Excel® 2010 and 2013

1.Start by formatting numbers to the time format.  (See the Excel layout below.)

  • Highlight the range B3:B9.
  • Right click and select format cells.
  • Select custom under the number tab.
  • Select h:mm:ss then select OK.

2.To round off to the nearest hour.

  • Place the cursor in cell C3.
  • Type =MROUND(B3,h1:00).
  • Press enter and copy the formula down.

3.To round off to the nearest minute.

  • Place the cursor in cell D3.
  • Type =MROUND(B3,h0:01).
  • Press enter and copy the formula down.
1

The time values have been rounded to the nearest hour or minute.  Only minutes or seconds that are greater than or equal to 30 are rounded off.

By

Minimize Data Entry Errors by Utilizing the Calendar Drop Down List

15

Post Last Updated: 10/15/2015 1:53:24 PM 

Ensuring data integrity by limiting data entry errors in a Microsoft® Excel® worksheet can be done by adding a calendar drop down list.  Instead of entering dates manually you can select them from the drop down list. Just follow the 10 easy steps below:

Applies to: Microsoft Excel 2007, 2010 (32-bit) and 2013 (32-bit)

The screen shot below will be used for this example.

tip-1

1. Activate the developer tab.

In Microsoft Excel 2010:

  • Select the File tab.
  • Select Options.
  • Select Customize Ribbon.
  • Tick the Developer box and select OK as per the screenshot below.
  • The Developer tab will now appear on the ribbon.

tip-2

In Microsoft Excel 2007:

  • Select the Office button
  • Select Excel Options
  • Select the Popular tab
  • Tick the Show Developer tab in the ribbon checkbox
  • The Developer tab will now appear on the ribbon.

2. Select the Developer tab.

3. Select Insert. A drop down list with icons for Form Controls and ActiveX Controls will appear.

4. Select the icon on the bottom right hand corner under the ActiveX Controls.

tip-3

5. More Controls will appear. Select Microsoft Date and Time Picker Control 6.0 (SP4)

6. Select OK.

tip-4

7. Your cursor will turn into a crosshair. Click and hold, draw a rectangle in cell C3.

8. Right-click on the Date Control and select Properties.

9. Type the cell where the date will be displayed next to linked cell as below.

tip-5

 

  • Ignore any error that you might get by clicking OK on the error message dialog box.
  • Close the properties window.
  • Select Design Mode to turn it off as per the screenshot below.

tip-6

10. When you select the date from the drop down menu the date in cell C4 will change.


tip-7

By

How to Remove Stubborn Spaces and Characters in an Excel Cell

22

Post Last Updated: 9/22/2015 9:53:49 AM 

If you have ever had problems sorting, filtering, or manipulating data in a Microsoft® Excel® workbook and realized that it was caused by stubborn leading spaces in the cells, the following steps will show you how to get rid of the spaces quickly next time.

If youre wondering what causes the spaces, here are two possibilities:

  • You may have mistakenly inserted extra spaces in your data by pressing the space bar more than once; or
  • You may have imported data from external sources which has embedded characters in the text that may not be easily noticeable making the results difficult to understand.

The next time you have this issue you can quickly remove unwanted spaces and characters using a combination of the TRIM and SUBSTITUTE functions. The TRIM function removes leading and trailing spaces in data but not characters. The SUBSTITUTE function replaces non-breaking characters with characters that the TRIM function can remove.

1. To remove leading and trailing spaces from the example workbook; the TRIM function will be used.

a. Select cell C2.
b. Type the formula =TRIM(B2).
c. Press Enter and copy the formula down.

2. As you can see the spaces have not yet been removed. This is because the TRIM and SUBSTITUTE functions must be nested.The stubborn characters set number 160 will be replaced with 32 then can be removed by the TRIM function.

Note:

  • In order to transmit information between computer systems a standard system that assigns numbers to letters of the alphabet, digits and characters was developed.
  • Therefore the character set number 32 represents a space.
  • The character set 160 represents a non-breaking space character that cant be removed by the TRIM function. It is commonly used in web pages.

tip-1

a. Select cell C2 and delete the formula.
b. Enter the following formula and then copy it down:

=TRIM(SUBSTITUTE(B2,CHAR(160),CHAR(32)))

tip-2

The stubborn leading spaces and characters will be removed so you can easily manipulate your data. Essentially, the SUBSTITUTE function in our example replaced the non-breaking character with a space which the TRIM function can remove.

(Appies to Microsoft® Excel® 2007, 2010 and 2013)


By

FREE e-book: Excel PivotTables and PivotCharts

17

Post Last Updated: 9/17/2015 1:33:26 PM 

11 tips on Excel PivotTables and PivotCharts!

A PivotTable can help you extract more meaningful information from your Sage 50 data. Its an interactive table that quickly summarizes large amounts of numeric data, which you can then analyze in detail using a PivotChart.

This useful e-book from Sage 50 Intelligence provides you with 11 tips and tricks to help you make the most of PivotTables and PivotCharts, giving you the ability to  analyze and present your Sage 50 data professionally.  The tips are focused around the following topics:
  • Slicers
  • Trendlines
  • Timelines
  • Rank Smallest to Largest
  • PivotTable Groups
  • PowerPivot add-in

    DOWNLOAD NOW!

By

Excel Tip: Creating First and Last Name Columns from a Full Name Field

17

If you need to split your employee names (or FFL player names!) in Excel as shown below:

 

Employee Name

Last Name

First Name

Johnson, Calvin

Johnson

Calvin

Cobb, Randall

Cobb

Randall

Murray, DeMarco

Murray

DeMarco

Nelson, Jordy

Nelson

Jordy

Luck, Andrew

Luck

Andrew

 

Here are the formulas:

 

Name

Last Name

First Name

Johnson, Calvin

=LEFT(A2,FIND(",",A2)-1)

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

Cobb, Randall

=LEFT(A3,FIND(",",A3)-1)

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

Murray, DeMarco

=LEFT(A4,FIND(",",A4)-1)

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

Nelson, Jordy

=LEFT(A5,FIND(",",A5)-1)

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

Luck, Andrew

=LEFT(A6,FIND(",",A6)-1)

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

 

To understand what these formulas are doing lets break them down.  There are 4 individual Excel functions being used:

 

1.       LEFT(text,[num_chars]) returns the left most number of characters from the start of a text string: if all last names were the same length we could just use the formula =LEFT(A2,7) and it would return Johnson as the Last Name in the first row of the example above

 

2.       RIGHT(text,[num_chars]) returns the right most number of characters from the end of a text string: if all first names were the same length we could just use the formula =RIGHT(A2,6) and it would return Calvin as the First Name in the first row of the example above

 

3.       LEN(text) returns the number of characters in a text string the LEN(A2) segment of the formula used to return the First Name would return the number 15 if used by itself in the first row of the example above

 

4.       FIND(find_text, within_text) - returns the starting position of one text string within another text string the FIND(,,A2) would return the number 8 if used by itself in the Last Name column of the first row of the example above and FIND( ,A2) would return the number 9 if used by itself in the First Name column of the first row of the example above

 

Therefore if we breakdown the First Name formula (since its the more complex of the 2) it is doing the following:

 

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

 

   Where A2 = Johnson, Calvin

   Where LEN(A2) = 15 (Johnson is 7 characters + 1 for the comma + 1 for the space + 6 for Calvin)

   Where FIND( ,A2) = 9 (the space is the 9th character from the left)

 

We then have RIGHT(A2,6) which returns the 6 characters from the end of the string which equals Calvin!  And now this formula can easily be copied down your column to return the First Name for everyone on your list.

 

A little challenging to put into words but youll find it simple to use once you set it up!

 

By



Rss-Logo Subscribe in RSS
Email
Subscribe by Email



Latest Posts
How to Remove Blank Cells in Your Excel Spreadsheet..
How To Easily Hide Zero Values in Excel..
Remove Hyperlinks from an Excel Worksheet..
Use the Excel MROUND Function to Round Off Time Values..
How to Rearrange Data Table Columns in Excel ..

26
Posts by Date
 2017

 2016

 2015

 2014

 2013

 2012


Tag Cloud



Share
RSS feed
Atom feed
Bookmark
Google
Delicious
Digg
MySpace
Facebook
Diigo
Yahoo
Stumble
Reddit
My Aol
LinkedIn

Bookmark and Share
  Powered by WorldWide Merchant


© 2018 Computerized Business blog all rights reserved.

Cba Greenbar

Home
Products

Services
Resources
About
Contact

Sage 50 2018
Sage 50 2018
VersAccounts ERP
Checks & Forms

Sage 50 Support
Peachtree Support

Tech Support
Sage 50 & Peachtree Training
Management Consulting
Bookkeeping
Merchant Services

Computerized Business Associates

4830 West Erie Street
Chandler, AZ 85226
480-945-4455 or 800-339-8224


CBA Peachtree Newsletter Signup Image
Get tips and news from Sage 50 (Peachtree) experts in your inbox
Master Certified Sage Consultants
Arizona, Nevada, California, Nationwide, Worldwide
This website is owned by Computerized Business Associates. Computerized Business Associates is independent from Sage and is not authorized to make any statement, representation, or warranties or grant any license or permission on behalf of Sage regarding any product, service, or website content. Certain materials made available on or through this website are owned by Sage and cannot be used without the prior written permission of Sage.
© 2018 Computerized Business all rights reserved.