Don’t stay without text

When someone tells you, “I’m left without text” this usually means a state of shock and confusion. Ignoring text related functions will not leave you in shock, but you will certainly be deprived of some useful tools. These functions are numerous, and in the “recipe” that follows I’ll describe these that allow merging of two or more texts into one, converting text to number or number in text, determining the length of text …

As you know, in order to enter some text we can start directly by entering data in the cell, and Excel will automatically recognize it, provided it is a textual information. In case you enter numbers, or something that resembles a formula (starts with “=”), data that resembles a date, a logical constant … things are a little bit different! Then it is necessary, in front of a sequence of characters, to put an apostrophe (‘) in order for Excel to format the contents of the cell as text.

Inside one cell, sometimes there is a need for connecting multiple character sequences (strings). The “&” operator is used for this purpose. So, for example, if you enter in a cell:

“Petar” & “Petrovic”

after finishing process of entering, the contents of this cell will become “PetarPetrovic”. Attention, name and surname are merged! This is because we need to add a space sign between them if we want them to be separated. For example:

“Petar” & ” ” & “Petrovic”

Instead of a string we could also give a reference to a cell or cell name, if it exists as such. If we are joining together more cells, it can be useful to use the CONCATENATE function:

CONCATENATE (<string₁>,…,<string_n>)

This function concatenates all strings, given as arguments, into one. In newer versions it will be replaced with function CONCAT with the same syntax (and a shorter name). For merging strings you can also use TEXTJOIN function, which has a syntax:

TEXTJOIN (<delimiter>,<ignore blank>,<string₁>,…,<string_n>)

This function merges multiple strings into one which are divided by a given delimiter. First argument is a delimiter, which is usually comma, space or some other character. Second argument is 0 (if empty strings are not omitted) or 1 (if they are omitted). At the end we enter strings or cell addresses which should be merged.

If we want to calculate the length of the string we will use LEN function, which has a syntax:

LEN(<string>)

If you want to repeat a string a specified number of times, you will use the REPT function:

REPT (<string>,<number of repetitions>)

For example, the formula:

=REPT(“A”,5)

gives the result string “AAAAA”.

If we want to convert a string into numerical value, in order to use it for calculation, we’ll use the VALUE function:

VALUE(<string>)

There is also a function that converts a number into text:

TEXT(<value>,<format>)

First argument is a numerical value, and other is a number format given under quotation signs.

There are two more functions that deal with conversion. Syntax of the first one is:

N(<value>)

This function converts numeric values into numbers, dates in ordinal numbers, text in value 0, and logical values in their numeric equivalents (TRUE = 1, FALSE = 0). Similarly, there is a function:

T(<value>)

This function changes given argument into a text. If the argument is text the value remains unchanged; otherwise it returns an empty cell.