562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Fourteen new functions coming to Excel
Part two of a three-part series of blogs
Get a preview of the 3 text functions and 11 array functions coming to your version of Excel very soon!
Suppose you have the following data:
You want to get each person's first name and surname, the start of their postcode and the things they like.
Previously you would have to do the first part of this by using FIND or SEARCH to find the position of the first space in a list, then using LEFT, MID or RIGHT combined with LEN to extract data.
You can now solve the above problem using these nifty functions:
|Function||What it does|
|TEXTBEFORE||Extracts text before a specified delimiter|
|TEXTAFTER||Extracts text after a specified delimiter|
Both functions have these arguments:
|1||text||Compulsory||The text you're extracting from.|
|2||delimiter||Compulsory||The delimiter you're searching up to/from.|
|3||instance_num||Optional||Which instance of the delimiter to use (by default you extract up to or from the first instance that you find). This can even be negative, as in the example below.|
|4||ignore_case||Optional||Whether you want to make ignore case-sensitivity (by default you don't, so everything is case-sensitive).|
So here's how you could extract the names of each person:
This assumes that a range called Person has been created to refer to A2:A4).
You can use a negative instance to search from the end of a string, rather than from the beginning:
This function will extract text from the last backslash character onwards (or to put it another way, from the first backslash character encountered when you read from the end of the string of text).
This is a function which returns multiple values which spill into a dynamic array (something which wouldn't have been possible in old versions of Excel). Here's an example:
The function splits the list of likes into the component words.
The TEXTSPLIT function has these arguments:
|1||text||Compulsory||The text you're extracting from|
|2||col_delimiter||Compulsory||The delimiter you're using to split the text|
|3||row_delimiter||Optional||An optional row delimiter (see below for what this means)|
|4||ignore_empty||Optional||Whether to create a blank cell if a delimiter appears twice in a row (the default is not to)|
|5||pad_with||Optional||What to put in any blank cells (this is only relevant if you include the row delimiter - see below)|
Here's an example of the use of a row delimiter:
When the function encounters a \ character it moves on to a new column; when it meets a space, it moves on to the next row. Any unused cells are filled with xxx (normally they would show #N/A).
While I can easily see that splitting text across columns might be useful, I'm struggling to see why you'd also want to split it down rows!
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street