Sunday, June 8, 2014

Ctrl+Shift+_ Excel Shortcut Erase The Selected Lines Border Of Cells

In Excel spreadsheet, if you want to erase lines border off. You can use shortcut key to apply the task as method below:

Method:

• Select the active cell(s) where you want to line off the border (e.g. as the image below is B3 : D7)
• Press the "Ctrl" button on your keyboard and keep it hold.
• Press the "Shift" button and keep it hold too.
• Press the "_" button. (underscore)

Then release all the three buttons.


The square outline border will be off immediately.

Note:

This shortcut will help you to erase a square border with only press the three shortcut keys.

Function Key
Result Description
Ctrl + Shift + _
Border line off shortcut key.

Did you find this article useful?

Michael Leng is Thai, has a BA in Hotel management from Ramkhamhaeng University. He had worked for some hotels. Currently he works in logistics field, Bangkok, Thailand. Because of loving in English,  he has written some his own blogs in English-he really wants any feedback about his English he has posted.

See more another tips:
You might like my another blogs:

Removes Unwanted Spaces Leading, Trailing And Between Words From The Text String.

In Excel spreadsheet, if you have typed some extra spaces between words. Therefore, you can use TRIM(text) function in order to get rid of those unwanted spaces. This function will erase all extra spaces but except for single spaces between them.

In addition the formula =TRIM(text) function will remove leading and trailing spaces from the text as well.

Syntax

TRIM(text)

How to use formula:

To type:

1] =TRIM into any cell.
2] (
3] Select the cell wanted to be trimmed. (See the image below as example by putting cell A1)
4] )

It's done.

Text: the text in any cell from which you want spaces deleted.

See an image example below for understanding easily.



Did you find this article useful?

See more another tips:
You might like my another blogs:

Saturday, June 7, 2014

VLOOKUP Searches For Value From Table Array In The First Column Return Value The Same Row

In Excel table with data entry, if you want to look for a value from a table array in the first column you refer to and get return an equivalent value in the same row from another column in the table array.

This case, suppose you will search for the data from the table array from the right column and returns data associate in the left column and the same row.

The formula to use is “VLOOKUP”

Formula Syntax

=VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)


* Data in the table array (F column) have to being sorted by ascending order, otherwise, the return value may give an unexpected/incorrect value data.

Do you find this article useful?

See more another tips:
You might like my another blogs:

Formula “LOOK UP - Array Form” Automatically Looks Approximate Match Values First Row/Column

Now I am going to tell you how the LOOKUP “Array Form” works. This form automatically looks in the first row or column and returns a value in the same position what the first row or column says.

i] This formula only works if the values: number, text, etc…in the first column table have been sorted in ascending order.


*See they have been sorted from b, c, e, f and g

ii] If there are the same data values in the first column, this formula will return the value that match what the value in the lowest cell in the first column.


* Notice the returned value is 8 (Not 6).

iii] If the lookup_value is less than the value in the first column, it will return value mistake or # n/a.


* Because non "a" in the c column.

Do you find this article useful?

See more another tips:
You might like my another blogs:

Thursday, June 5, 2014

Formula “LOOK UP” For Using Approximate Match Values Sorted Ascending Order In a List

Microsoft Office Excel, the LOOKUP function to operate worksheet has two forms. One is a “VECTOR” form and the other is an “ARRAY” form.

Today article, I only talk about a VECTOR form. The formula criteria of vector form looks only in a one-row or one-column range for a value and responses a value from the same position in another one row or one-column that you refer to.

Notify:
  • This formula only works if the values: number, text, etc…in the first column table have been sorted in ascending order.
  • If there are the same data values in the first column, this formula will return the value that match what the value in the lowest cell in the first column as an image shown below.

Do you find this article useful?

Michael Leng is Thai, has a BA in Hotel management from Ramkhamhaeng University. He had worked for some hotels. Currently he works in logistics field, Bangkok, Thailand. Because of loving in English,  he has written some his own blogs in English-he really wants any feedback about his English he has posted.

See more another tips:
You might like my another blogs:

Tuesday, June 3, 2014

Method of Extract the Specified Characters from the End of Text String Content

In this tutorial, you will know the method of extracting the specified characters from the end of the text string in your Excel spreadsheet.

Syntax

RIGHT(text,num_chars)
  • RIGHT returns the first character or characters in a text string, based on the number of characters you specify.
  • RIGHT always counts each character, whether single-byte or double-byte, as 1 (one), it does not matter what the setting of the default language as is.
  • TEXT is the text string that contains the characters you want to extract. Whatever text string consists of single-byte or double-byte characters, the following formula represents each one as only 1 string abstract.
  • Num_chars specifies the number of right characters that you would like to extract.
  • Num_chars must be greater than or equal to zero. If it is less than zero, the result will display “#VALUE!” that means it comes out with nothing.
  • If num_chars is greater than the length of text, Right returns all of text.
  • The result will return with 1 (One), If num_chars is omitted.
Method of using formula:

In cell A2: Fill out the data [I want to extract BIRD]
In cell B2: Type this formula into it [=RIGHT(A2,4)]


You should have the text "BIRD" remaining in your cell "B2"

Do you find this article useful?

Michael Leng is Thai, has a BA in Hotel management from Ramkhamhaeng University. He had worked for some hotels. Currently he works in logistics field, Bangkok, Thailand. Because of loving in English,  he has written some his own blogs in English-he really wants any feedback about his English he has posted.

See more another tips:
You might like my another blogs:

How To Extract The Specified Characters From The Left Of The Text String In Active Cell

What to do if you want to extract the specified characters from the left of the text string in the active cell in your Excel worksheet.

Do not worry, be happy, we have the formula get task done as below.

Syntax

LEFT(text,num_chars)
  • LEFT returns the first character or characters in a text string, based on the number of characters you specify.
  • LEFT always counts each character, whether single-byte or double-byte, as 1 (one), it does not matter what the setting of the default language as is.
  • Text is the text string that contains the characters you want to extract. Whatever text string consists of single-byte or double-byte characters, the following formula represents each one as only 1 string abstract.
  • Num_chars specifies the number of characters you want LEFT to extract.
  • Num_chars must be greater than or equal to zero. If it is less than zero, the result will display “#VALUE!” that means it comes out with nothing.
  • If num_chars is greater than the length of text, LEFT returns all of text. Such as your text string consists of ten characters, if you input the number 11 into the formula. The result will return the whole of text you have.
  • The result will return with 1 (One), If num_chars is omitted.

Do you find this article useful?

See more another tips:
You might like my another blogs:

Delete Unwanted Characters and or Blank Space From The Left Side Of Contents In Active Cell

Today, I am going to tell you on how to move out some specified number of the characters that you have entered into active cell in your Excel worksheet.

The functions for this task being done are “RIGHT” and “LEN”

For more ease to deal this delete the unwanted specified characters; follow the example and image below:

Within an active cell A1, input the data entered, “Please leave alone A”, -- this data entered set is with the twenty characters in text string. It is including between seventeen letters and three of bank space between each word.

Now, you are going to get rid of the all the characters but A

Here we go through the step by step:
  • In cell A1, write down the text: "Please leave alone A"
  • In cell B1, fill in this formula: =RIGHT(A1,LEN(A1)-19)
Result:

Eventually, the all the characters will be moved out but left only the letter “A”.
Take this formula to apply and use for your own task.

Do you find this article useful?

See more another tips:
You might like my another blogs:

Sunday, June 1, 2014

Removes Characters and Blank Space From The Right Side Of Contents In Cell Of Excel Worksheet

In the active cell, you have some contents inside it. For more understanding it easily, suppose you have the following data in your Excel worksheet:

In active cell A1, it contains the data, “Remove A”, -- this data entered is composing with seven characters and one blank space between –e and A.

Now, supposing that you want to delete the last two characters from the content, the "Blank Space" and "A" letter.

Use this formula as an image shown below: 

In cell A1, to type the text: "Remove A"
In cell B1, fill out the formula: =LEFT(A1,LEN(A1)-2)


Result:

There will be just the only word “Remove” remaining in the active cell. The last two characters, the "Blank space" and "A" letter, are deleted.

Do you find this article useful?

See more another tips:
You might like my another blogs: