Continuing  our data manipulation, these further examples can be applied to a cell to extract all kinds of data, and when used together can be particularly powerful when combined with the FIND command.  Part 3 (it could even be Part 4) will cover this and more! 🙂

 

An error is returned on a formula that is using a number extracted from a formula in Excel, why?

=VALUE(text)

When manipulating text using the aforementioned formula, it is common to extract numbers from an alphanumeric cell via manipulation or formula, often with the alphanumeric attribute still intact.  The use of VALUE  then forces any cell referenced to be treated as a number, where as formating the cell will not.

An example is where a formula displays a result and is then referenced by another formula. Second formula may error unless the preceding result is reference through the value statement.

 

How do I change case from lower to upper case in Excel?

=UPPER(text)

Turns selected text into Upper case.

Example:

A2 contains the example formula; it references A1 as its source. Notice the resulting output is in all upper case.

 

How do I change case from upper to lower case in Excel?

=LOWER(text)

Turns selected text into Lower case.

Example:

A2 contains the example formula; it references A1 as its source. Notice the resulting output is in all lower case.

 

How do I combine the contents of multiple cells to make a sentance?

=CONCATENATE(text, text, text…) or the continued use of ‘&’

(Where “text” = cell reference)

Can be used to combine multiple cells of data to form a text string

Example:

A2 contains the example formula; it references A1, B1, C1 and D1 as its source. To add spaces in this illustration I have added a space character between quotation marks (“ “).

Or:

Again A2 contains the example formula; it references A1, B1, C1 and D1 as its source. Removing “concatenate” you can combine strings with the example above using & instead.

I will leave you to judge which is your preferred, neater solution.