Text Functions: Combine

Quick reference

• 00:05 In this example I have an extract generated by a mobile application.
• 00:09 The function of the mobile app is to create an inventory of books.
• 00:13 But when I imported into Excel, the columns with the author name and
• 00:17 the number of pages got combined.
• 00:20 Let's start cleaning this up by extracting the author from that text string.
• 00:26 Now, I know a few text functions that might be able to help and
• 00:29 mid is a good place to start.
• 00:31 Mid will extract text from that string in with mid the first
• 00:36 argument is just the text string that's over there.
• 00:41 But where do I start?
• 00:43 The name of the author starts consistently in the same spot because before
• 00:48 the author name we have the word author and then a colon,
• 00:52 that's consistent for all the fields.
• 00:55 We can use the len function to get the correct start point.
• 01:00 The start number is going to be the number of characters in the word author plus
• 01:05 the colon, plus one, because I want to start extracting right after the colon.
• 01:12 So we type a comma, and then what's the number of characters,
• 01:17 what's the total length of the name?
• 01:21 This is going to be tricky, it needs to be dynamic because each author has
• 01:25 a different number of characters in their name.
• 01:28 So how do I know what the number of characters is in each row?
• 01:33 Another thing that's consistent in these rows is
• 01:35 that the word pages starts right after the author's name.
• 01:39 So, if we can figure out the length of the text from the beginning,
• 01:44 right up to the point where pages begins, but then subtract the author and
• 01:50 the colon we'll have the proper length of each name.
• 01:56 The way we can figure this out where pages begins, is to use the find function.
• 02:02 But keep in mind, we need to find pages.
• 02:04 If we said well let's just find the uppercase P, it might not work because
• 02:09 an author name like Peter Carey down below also has an uppercase P.
• 02:15 That would not give us the results we need.
• 02:17 So let's find pages within the string.
• 02:20 When Excel locates that first letter, the P in pages, we have to subtract
• 02:25 one character because I want to stop extracting before the P begins.
• 02:31 Also, I need to deduct the length of the word author from the extraction.
• 02:36 I don't want author and colon, I just want what comes after.
• 02:40 So we subtract len, author and the colon.
• 02:46 Now my mid function is complete.
• 02:48 And let's review here.
• 02:50 Mid will extract something from the middle of A5, where does it start?
• 02:56 The length of author,
• 02:58 colon plus one means it will start right after the colon in each string.
• 03:05 And how many characters does it extract?
• 03:07 It's the length of this whole part from the beginning to just before pages,
• 03:12 but when we subtract the length of author and
• 03:15 colon, we'll have the correct length for each row every time.
• 03:20 And you can see when we hit enter, we get Carol McGinnis Kay, perfect.
• 03:25 Copy that for the whole column, and we have all of our author names,
• 03:30 tricky, right?
• 03:32 But we did it.
• 03:34 In the exercise, I've given you the same data set, but
• 03:37 I've also included exercise two and a solution.
• 03:40 Exercise two has a bit of a longer problem, and
• 03:43 it requires you to get a publisher and author, and the number of pages.
• 03:49 So enjoy practicing.

Lesson notes are only available for subscribers.