Tuesday, 31 January 2017

How to remove space in excel after text - TRIM

How to remove space in excel after text

TRIM
Original Text
Trimmed Text
  ABCD
ABCD
 =TRIM(B2)
  A   B   C   D
A B C D
 =TRIM(B3)
   Alan        Jones
Alan Jones
 =TRIM(B4)
ABCD
ABCD
 =TRIM(B5)

What Does It Do ?
This function removes unwanted spaces from a piece of text.
The spaces before and after the text will be removed completely.
Multiple spaces within the text will be trimmed to a single space

Syntax=TRIM(TextToTrim)

Formatting
No special formatting is needed.

Wednesday, 11 January 2017

How to use the Excel COUNTBLANK function



COUNTBLANK

Range To Test
Blanks
2
2
 =COUNTBLANK(C4:C11)
Good
5
1

1-Jan-17

3


What Does It Do ?
This function counts the number of blank cells in a range.

Syntax

=COUNTBLANK(RangeToTest)

Formatting

No special formatting is needed.

Example

  • The following table was used by a company which was balloting its workers on whether the company should have a no smoking policy.
  • Each of the departments in the various factories were questioned.
  • The response to the question could be Y or N.
  • As the results of the vote were collated they were entered in to the table.
  • The =COUNTBLANK() function has been used to calculate the number of departments which have no yet registered a vote.
Admin
Accounts
Production
Personnel
Office 1
Good
Bad


Office 2

Good
Good
Bad
Office 3




Office 4
Bad

Bad
Bad
Office 5
Good

Good

Office 6
Good
Good
Good
Bad
Office 7

Bad
Good

Office 8
Bad
Bad
Good
Good
Office 9


Good

Office 10
Good
Bad

Good

Votes not vet registered :
16
 =COUNTBLANK(F19:I28)
Votes for Good :
14
 =COUNTIF(F19:I28,"Good")
Votes for Bad :
10
 =COUNTIF(F19:I28,"Bad")

Monday, 9 January 2017

How to find true or false in excel exactly


True or False in excel Exactly


Text 1
Text 2
Result
Good
Good
TRUE
 =EXACT(C4,D4)
Bad
bad
FALSE
 =EXACT(C5,D5)
Goodbye
Good Bye
FALSE
 =EXACT(C6,D6)

What Does It Do?


This function compares two items of text and determine whether they are exactly the same.
The case of the characters is taken into account, only words which are spelt the same and
which have upper and lower case characters in the same position will be considered as equal.

Syntax

  •  =EXACT(Text1,Text2)
  •  Only two items of text can be compared.

Formatting

  • If the two items of text are exactly the same the result of TRUE will be shown
  • If there is any difference in the two items of text the result of FALSE will be shown

Example

  • Here is a simple password checking formula
  • You need to guess the correct password
  • The password is the name of a colour, either red blue or green
  • The case of the password is important
  • The =EXACT() function is used to check your guess

Guess the password :
Red
Is it correct :
No

(To stop you from cheating, the correct password has been entered as a series of =CHAR()
functions, which use the ANSI number of the characters rather than the character itself!)
Its still very easy though.

Easy Excel Shortcut and Formulas

How to concatenate in excel

CONCATENATE

Name 1
Name 2
Concatenated Text
Alan
Kerbyson
AlanKerbyson
 =CONCATENATE(C4,D4)
Brian
Beach
BrianBeach
 =CONCATENATE(C5,D5)
Frank
Staniland
FrankStaniland
 =CONCATENATE(C6,D6)
Jan
Semiras
Jan Semiras
 =CONCATENATE(C7," ",D7)
Luke
Collins
Collins, Luke
 =CONCATENATE(D8,", ",C8)
Rita
Narang
Narang, Rita
 =CONCATENATE(D9,", ",C9)

What Does It Do?
This function joins separate pieces of text into one item.

Syntax
 =CONCATENATE(Text1,Text2,Text3...Text30) Up to thirty pieces of text can be joined.

Formatting
No special formatting is needed, the result will be shown as normal text.

Note
You can achieve the same result by using the & operator.
Name 1
Name 2
Concatenated Text
Alan
Kerbyson
AlanKerbyson
 =C25&D25
Brian
Beach
BrianBeach
 =C26&D26
Frank
Staniland
FrankStaniland
 =C27&D27
Jan
Semiras
Jan Semiras
 =C28&" "&D28
Luke
Collins
Collins, Luke
 =D29&", "&C29
Rita
Narang
Narang, Rita
 =D30&", "&C30

Tuesday, 3 January 2017

How to remove non printable characters in excel

How to remove non printable characters in excel

CLEAN

What Does It Do?

This function removes any non printable characters from text. These non printing characters are often found in data which has been imported from other systems such as database imports from mainframes.

Syntax

=CLEAN(TextToBeCleaned)

Formatting
  • No special formatting is needed. The result will show as normal text.