Text


Understanding the Format


 


To convert cells containing numbers to text, apply the Text format then press F2 to re-enter the data.


Editing Text

You can quickly move between words in a cell by holding down the Ctrl key and pressing with the left or right mouse button.


Replacing Text

The easiest way to replace text in your cells is to use the (Edit > Find) dialog box.



Useful Characters

CHAR(42) = comma
CHAR(32) = space
CHAR(10) =



Important

When you enter text into a cell, if the contents will automatically spill over to the next adjacent cell assuming that the cell is empty. If the cell is not empty then the text will be truncated to the width of the column.
In Excel 2002 any numbers which are preceded with an apostrophe will have a Smart Tag attached to them. This will allow you convert the 'text' value to an actual number if you want.
Text entered into a cell is often referred to as a 'string'. It may also be referred to as a 'text string'.
Each uppercase character is exactly 32 characters lower than its corresponding lowercase characeter.


Entering Text

By default text is aligned and numbers are right aligned.
A cell can hold up to 32,000 characters of text although it is not recommended to have more than 255.
Not all the text functions will return the correct answer when a string contains more than 255 characters.
With text wrap formatting set in a cell (Format > Cells)(Alignment tab, Wrap Text), sometimes you want to force a line break at a certain point. You can use (Alt + Enter)
SS


You can enter tabs and carriage returns in a cell to make the text easier to read.
To enter a tab, press (Ctrl + Alt + Tab).
To enter a carriage return press (Alt + Enter).
Carriage retursn cause the contents of the cell to wrap within the cell.



Numbers as Text

When you enter a number into a cell it is automatically right aligned.
When you enter text into a cell it is automatically left aligned.
There may be times when you have entered a number but you want it to be considered as text.
There are two ways you can achieve this:
1) Select (Format> Cells)(Number tab) and select the Text category.
2) Place an apostrophe before the actual number. Don't worry this apostrophe is not displayed it just automatically formats the cell as Text.
Even though a cell is formatted as text it can still be used in 'some' functions and formulas.


SS A1 + 5 - when A1 is formatted as text
SS SUM() - does not work !!


Data (i.e. values) you import into Excel is often treated as Text. This can be quickly rectified by multiplying the data by 1.
SS - Example - how to multiply by 1



Concatenating Text

Excel uses the ampersand "&" character for string concatenation.
The concatenation is a word that means to join two strings together.
This can be used in formulas to combine the contents of multiple cells.
SS
There is also a CONCATENATE function.
You can add spaces by enclosed them in speech marks
SS
Alternatively you can use the CHAR(32) to represent a space
SS



Using TEXT to format values used in a text string


"The total sales last year was" & ""
"This page was printed on : " & ""
Todays date is :Text(NOW(), " "_ & and time & Text(now(), " ")


Comparing Text

Comparing text strings for equality can be done using the "=" operator but it is important to remember that this is not case sensitive
SS ("better solutions" = "Better Solutions" when is cells
If you want to perform a case-sensitive text comparison then use the EXACT function.
SS


Proportional and Non Proportional Fonts

If the default font is a non-proportional (fixed width) font, such as Courier, 8.43 characters of any type (numbers or letters) fit into a cell with a column width of 8.43 because all Courier characters are the same width.
If the font is a proportional font, such as Arial, 8.43 integers (numbers such as 0, 1, 2, and so on) fit into a cell with column width of 8.43.
This is because numbers are fixed-spaced with most proportional fonts.
However, because letters are not fixed-spaced with proportional fonts, more "i" characters fit and fewer "w" characters fit.
When you change the width of a column to a fractional number, the column width may be set to a different number depending on the font used in the Normal style.
For example, with a Normal style font of Arial, if you attempt to change the width of a column to 8.5, the column is set to 8.57 or 65 pixels.
This behavior occurs because of the translation of font characters to pixel units.
Fractional pixel units cannot be displayed; therefore, the column width rounds to the nearest number that results in a whole pixel unit.




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext