Microsoft has released four awesome new text functions into Excel
All four are really useful additions to the Excel function set, simplifying the way we tackle data analysis and financial modelling challenges. Let's have a look at each of them in turn.
The TEXTJOIN function concatenates (or joins) values together from cells or ranges of cell.
This function is a great improvement on the CONCAT and CONCATENATE functions as it allows you to include a delimiter and to choose whether or not to ignore empty cells.
See our full article on the TEXTJOIN function here.
TEXTSPLIT splits a text string by using column and row delimiters. The TEXTSPLIT function works the same as text to column, but in a formula rather than through a wizard. It allows you to split across columns and/or down by rows.
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
The ability to split text by formula across rows and columns is incredibly useful.
See our full article on the TEXTSPLIT function here.
TEXTBEFORE returns text from a text string that occurs before a given character or string.
=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
See our full article on the TEXTBEFORE function here.
TEXTAFTER returns text from a text string that occurs after a given character or string.
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
See our full article on the TEXTAFTER function here.
In short I absolutely love these new text functions, especially TEXTJOIN and TEXTSPLIT.
These are a great set of new functions and a really useful addition to any data analyst's or financial modeller's toolkit.
This series of articles focuses on new features soon to be released on Excel. This is based on announcements by the Microsoft Office Insider team.
See other articles in our Future of Excel series here.