Hi, this is Wayne again with a topic “The Excel TEXTSPLIT TEXTBEFORE and TEXTAFTER Functions”.
In this Excel video, I will show you how to use the Excel text split function and as a bonus, you’ll also learn text before and text after, let’s get started so here I am in a spreadsheet and you can see it’s a list of people their ages. Their job positions and their salaries, the problem is this – was copy pasted from another source, and so all of that data is inside column a. I really would like the data spread out over all four of these columns. So how could we do that? There are a few different ways, including using the text to columns wizard, but let’s look at how we can use the text split function to do the same thing, but by using a formula – and the first thing I want to do – is I’m going to go here To column, B and rightclick and choose insert that gives me another column here, just to the right of my name column, and I’m also going to name this name name now: I’ll click on Cell B2 and I’ll type equals text split I’ll put in my left parenthesis And next Excel is looking for some text in a Cell. In this case, this is the text that I want to work with, so I’ll click on A2.
Next, I need to put in a comma and Excel is now looking for a column delimiter, and basically what this means in this case is Excel wants to know. What is it in this text that I want to use to separate one piece of information from another? In this case, it’s commas, so in quotation marks I’m going to put a comma I’ll close, the quote, and at this point there are other things I could put into this formula. But this should do the trick so I’ll put in the right parenthesis. Tap enter on the keyboard and look Jane Smith has been put here in column b, 40 has been put in column C and so on.
Now, unfortunately, look what happened to salary, because in the original text there is a comma used to show thousands Excel interpreted that as $ 80 salary instead of 880,000. So that’s something to watch out for any use of your column. Delimiter will separate the text. That’S before it from the text, that’s after it, so one way to fix that is to just take out those commas tap enter.
And if we look over here at the right notice that the salary for Jane Smith has been dynamically updated, I’m going to go back here to column B and I’ll click on B2 to select Jane Smith. And I’m just going to put my mouse here in the lower right corner of cell B2 and I’ll click and hold the click on the little autofill handle. That’S green and drag down to the bottom of my data release the mouse button and now the rest of my data in column.
A has been split, using text split into the appropriate columns. Now the only problem is, I have two name columns watch what happens if I rightclick on column A and choose delete, that’s going to cause an error message, and you can probably understand why I’m going to hold controll and tap Z to undo that the reason deleting Column A causes, an error message is because the rest of the data in the spreadsheet relies on what’s in column A so. I can’t just delete this important information, so instead, what I’m going to do is click and drag from B E2, all the way through to E11, and let’s pretend, like I’ve, fixed the salaries for all of these people with that range selected, I’m going to copy it I’Ll just hold controll and tap C to copy, and now I’m going to paste the data – and I think I’ll do that here in cell B13, so I’ve clicked there and now I’m going to paste. But, instead of doing a typical paste, I’m going to go to the Home tab in the clipboard group and I’m going to click on the bottom part of the p paste button. I can click there and choose paste values. There are three options, but I’ll just go with this first I’ll click, and now the data has changed.
It’S no longer reliant on this Source information here in column A so. At this point, I could right click on a delete, the column and then I could click and drag on Rows. 2 through 12 – in this case I’ll rightclick on one of the numbers and choose delete, and now my original data has been split and distributed across the columns.
The way I wanted it to be, let’s quickly, look at a second example here I have a list of new wave or synth pop bands, and this was copied and pasted, let’s say from the Internet or some other source and notice that all of these bands are Just all together in the same cell, each band name is separated just by a space. So let’s try using text split here in cell A4, I’m going to type equals text, split, left, parenthesis I’ll click. On A2, it selects the entire cell I’ll put in a comma and what’s the delimiter.
What is the thing that is separating each item in this case? It’S a space so I’ll put in a quotation mark and then I’ll tap space bar to put a space in there and then close quote. I should put in my right parenthesis tap enter, and each of these bands has been split from the others and distributed across The Columns now. What if I really preferred to have them all in column? A all I would have to do is go down here somewhere to column, a click type equals transpose, left parenthesis, and then I could put in the array or just click and drag to select the array either one whichever you find easier and then just tap, enter On the keyboard and that same list of data has now been put into one column as a list just like in the previous example.
This data is reliant on the original information here, and so I should copy paste it using paste special to paste it as values. Okay, let’s take a look at two related functions that are similar to text split and they are text before and text after so I’ve gone here to my example, three spreadsheet and you can see what we have here – a list of names, but I would like to Split them into first name and last last name, and yes, it’s true that there are other ways to accomplish this. But let’s accomplish this by using a formula so here on B2 I’ll type, equals text before left parenthesis and then I’ll click on Jane Smith, cell A2. I’Ll put in a comma next Excel is expecting a delimiter. What is separating these two words? Well, it’s a blank space, so I’ll put in a quotation, mark tap spacebar to put in a blank space and close quote and my right parenthesis I’ll tap enter on the keyboard. Now I have Jane, I can now click on B2 and I’m just going to double click on the autofill handle in the corner and that will apply the same formula all the way down the spreadsheet so that I have all first names.
Now I’m going to do the same thing, but using text after so here on C2 I’ll, just click type equals text after left parenthesis I’ll, select, A2 I’ll put my comma and once again the delimiter is a blank space. So I put that in put my right parenthesis, tap, enter and I’ll click on C2 double click on the autofill handle, and I have now used formulas to separate or split the text in column A so in this article. We’Ve looked at text split text before and text after.
I hope you found this video to be helpful. If you did please like follow And subscribe, and when you do click the Bell so you’ll be notified. When I post another video, if you’d like to support my channel the best way to do that is to join me as a channel member, you could also support me by clicking the thanks button below the video you could support me through my patreon account, or by Buying Channel merch and you’ll see information about those options in the description below the video speaking of supporting the Channel, I want to say thank you to my super techie Channel supporters. Thank you so much for the support you give thanks to that support. I’M able to continue making these educational videos.
Thank you. .