Separate Strings

Separate Strings

Separate Strings

This example teaches you how to separate strings in Excel.

The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9.

1. To get the first name, use the formula below.

Explanation: to find the position of the comma, use the FIND function (position 6). To get the length of a string, use the LEN function (11 characters). =RIGHT(A2,LEN(A2)-FIND(",",A2)-1) reduces to =RIGHT(A2,11-6-1). =RIGHT(A2,4) extracts the 4 rightmost characters and gives the desired result (Mike).

2. To get the last name, use the following formula.

Explanation: to find the position of the comma, use the FIND function (position 6). =LEFT(A2,FIND(",", A2)-1) reduces to =LEFT(A2,6-1). =LEFT(A2,5) extracts the 5 leftmost characters and gives the desired result (Smith).

3. Select the range B2:C2 and drag it down.

Refer: http://www.excel-easy.com/data-analysis.html

This example teaches you how to separate strings in Excel.

The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9.

1. To get the first name, use the formula below.

Explanation: to find the position of the comma, use the FIND function (position 6). To get the length of a string, use the LEN function (11 characters). =RIGHT(A2,LEN(A2)-FIND(",",A2)-1) reduces to =RIGHT(A2,11-6-1). =RIGHT(A2,4) extracts the 4 rightmost characters and gives the desired result (Mike).

2. To get the last name, use the following formula.

Explanation: to find the position of the comma, use the FIND function (position 6). =LEFT(A2,FIND(",", A2)-1) reduces to =LEFT(A2,6-1). =LEFT(A2,5) extracts the 5 leftmost characters and gives the desired result (Smith).

3. Select the range B2:C2 and drag it down.

Refer: http://www.excel-easy.com/data-analysis.html

Separate Strings

Date 4/800: Excel 2010 Freeze Panes

Date 4/800: Excel 2010 Freeze Panes

Date 4/800: Excel 2010 Freeze Panes

http://www.excel-2010.com/freeze-panes-in-excel/

http://www.excel-2010.com/freeze-panes-in-excel/

Date 4/800: Excel 2010 Freeze Panes

Date 4/800: Wrapping Text In Excel

Date 4/800: Wrapping Text In Excel

Date 4/800: Wrapping Text In Excel

27-09-2013

A useful tip about Bullet Points In Excel

This is a strange one. Although there is no bullet point command on the ribbon, there is a keyboard shortcut. Position the cursor where you need a bullet and press ALT-0149. You may need to insert a space before you start typing, otherwise it will look a little cramped. Unfortunately, there is no way to select text you’ve already typed and then apply bullet points to it (like you can do in Microsoft Word).

27-09-2013

A useful tip about Bullet Points In Excel

This is a strange one. Although there is no bullet point command on the ribbon, there is a keyboard shortcut. Position the cursor where you need a bullet and press ALT-0149. You may need to insert a space before you start typing, otherwise it will look a little cramped. Unfortunately, there is no way to select text you’ve already typed and then apply bullet points to it (like you can do in Microsoft Word).

Date 4/800: Wrapping Text In Excel

Date 4/800: Excel 2010 Sparklines

Date 4/800: Excel 2010 Sparklines

Date 4/800: Excel 2010 Sparklines

27-9-2013

If we want to show trends, we could use sparklines in Excel.

http://www.excel-2010.com/sparklines-in-excel/

27-9-2013

If we want to show trends, we could use sparklines in Excel.

http://www.excel-2010.com/sparklines-in-excel/

Date 4/800: Excel 2010 Sparklines

Date 4/800: Format number trong Excel

Date 4/800: Format number trong Excel

Date 4/800: Format number trong Excel

27-09-2013

This is an issue my wife often meets when she wants to display the number like '0123', it will be '123' in Excel

In the article the author has described the way to handle this.

Format cells -> Custom -> Type 0000.

Refer: http://chandoo.org/wp/2008/06/16/formatting-numbers-in-excel-few-tips/

27-09-2013

This is an issue my wife often meets when she wants to display the number like '0123', it will be '123' in Excel

In the article the author has described the way to handle this.

Format cells -> Custom -> Type 0000.

Refer: http://chandoo.org/wp/2008/06/16/formatting-numbers-in-excel-few-tips/

Date 4/800: Format number trong Excel

Date 4/800: dùng hàm Offset trong Excel

Date 4/800: dùng hàm Offset trong Excel

Date 4/800: dùng hàm Offset trong Excel

I often download software product sales information listed by country . I need to track
 revenues from Iran as well as costs and units sold, but the data about Iran isn’t always in
the same location in the worksheet . Can I create a formula that will always pick out Iran’s
 revenues, costs, and units sold?

I often download software product sales information listed by country . I need to track
 revenues from Iran as well as costs and units sold, but the data about Iran isn’t always in
the same location in the worksheet . Can I create a formula that will always pick out Iran’s
 revenues, costs, and units sold?

Date 4/800: dùng hàm Offset trong Excel

Useful date functions in Excel

Useful date functions in Excel

Useful date functions in Excel

Use today() function to get current date in Excel

How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?
The function WORKDAY(start_date,#days,[holidays]) displays the date that is the number of workdays (a workday is a nonweekend day) indicated by #days after a given start date.

Use today() function to get current date in Excel

How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?
The function WORKDAY(start_date,#days,[holidays]) displays the date that is the number of workdays (a workday is a nonweekend day) indicated by #days after a given start date.

Useful date functions in Excel

Rept function in Excel

Rept function in Excel

Rept function in Excel

This function help us repeat a text in a given number.

For example:

Here is the result:

This function help us repeat a text in a given number.

For example:

Here is the result:

Rept function in Excel

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMK69LJeBqjmpEyIFvmMmGDK_DTgBCdtCpcvqUqFARZU8y1sfZuZqYS-gL8wQu-oH9fPuJkGb8J0LgIcFdCXNPqiPq20BpfkNK0SaiJTSvpNXBhX3qMar5SwPyK5u3wKQAZjkKTmmOo5E/s72-c/Excel-Rept-function.png

http://www.uniteforsight.org/global-health-university/data-analysis

http://www.uniteforsight.org/global-health-university/data-analysis

http://www.uniteforsight.org/global-health-university/data-analysis

http://www.uniteforsight.org/global-health-university/data-analysis

http://www.uniteforsight.org/global-health-university/data-analysis

http://www.uniteforsight.org/global-health-university/data-analysis