Text Manipulation

Question 1

8

concatenate/joining text

Answer 1

Ampersand symbol (&)

• Select the cell where you want to put the combined data.
• Type = and select the first cell you want to combine.
• Type & and use quotation marks with a space enclosed.
• Select the next cell you want to combine and press enter. An example formula might be =A2&” “&B2.

CONCAT function

• Select the cell where you want to put the combined data.
• Type =CONCAT(.
• Select the cell you want to combine first.
• Use commas to separate the cells you are combining and use quotation marks to add spaces, commas, or other text.
• Close the formula with a parenthesis and press Enter. An example formula might be =CONCAT(A2, “ Family”).

Question 2

textbefore function

Answer 2

=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

Question 3

textsplit function

Answer 3

TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

text The text you want to split. Required.

col_delimiter The text that marks the point where to spill the text across columns.

row_delimiter The text that marks the point where to spill the text down rows. Optional.

ignore_empty Specify TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional.

match_mode Specify 1 to perform a case-insensitive match. Defaults to 0, which does a case-sensitive match. Optional.

pad_with The value with which to pad the result. The default is #N/A.

same as text to columns wizard

Question 5

9

string functions

Answer 5

extract a portion of a cell’s value/text

you can select a certain number of characters in a cell starting from a location (right, left, mid, etc.)

Question 6

5

large function

Answer 6

LARGE(array, k)

The LARGE function syntax has the following arguments:

Array Required. The array or range of data for which you want to determine the k-th largest value.

K Required. The position (from the largest) in the array or cell range of data to return.