Hi, I love to give you a challenge question. And this is one of those from the CDs. And let me give you the context. This is with respect to data cleaning. In case three, I have a data, which consists of some names with different allocation of spaces. And notice they end with a certain rule number, it could be membership number.

And the point is the membership numbers are of different varying lengths. So I want a trick, which gets me this data. In this form, I'm going to cut that data are pasted in the adjoining cell. And that is how I want the data to be presented in the final form. Now I know what you're thinking you might be thinking of a trick called text to column, isn't it? Well, let me tell you that text to column isn't going to help you why.

If you go to text to column, data text to column, you notice there are two options in case you have used it before. If not, please refer to my video On text to columns delimited it asks you what is the common delimiter? Is it space? Is it hyphen? Is it dot? What is it?

If I say space, it cuts the data in different columns, and it's very inconsistent. This is not what I was looking forward to. So you might tell me why why not be used fixed rate? Well, fixed width, as the name suggests, it is actually fixed columnar width. So since the data is not consistent, Len, I cannot use that technique. Why?

Because as you can see from the preview window, it gets me the data in different columns. So both these techniques of text to column isn't going to help me. Now what is going to help me You probably might be thinking, Find and Replace, okay, and roll edge. What do you do next? What, what is the character you're going to write to help you get rid of the numbers and get it pasted outside? Now some of you might also be thinking partly, why not you use a formula Right, this is something that we'll be discussing.

Or you may have watched the video on right formula, but right formula extracts the data. Only if you know the specific number of characters that needs to be extracted. This works fine, because it has four characters. But the same technique may not work for the third line item. Now, of course, there are ways in Excel by which you can cut the data into two different columns the way we wanted, but that's going to be a little tedious. For a normal user, I want a trick which you don't need to remember, which is not tedious, and which is very easy to use.

So easy that you can also teach your junior right away right now. Let me show you that. I copied the data. You also copy the data at the same time. And let me actually take the help of Word document. Yeah.

Well document I know what you're thinking, Excel and how come word Well, I'm going to ask you to paste it. Leave some rows and paste it again. And few moments you realize why I asked you to open the words document a blank Word document and paste the data. Once you did, so, you choose the first set of data press Ctrl H yes Ctrl H and Word document. Now, most of you would have used this while drafting legal documents or maybe agreements, but notice there is a button called more and within more there is something called special it says so many options within them. And one of which which attracts my attention is any digits.

So it gives you a code which is not usable in Excel. It this code represents all digits. So when I say find what all digit replace with nothing within this data only, I will say replace all it says we made 18 replacements in your selection. Okay, good. Do you want to search the rest of the document? Nope.

I just want to apply this technique on the first box. Like that is a technique. What technique would you apply in the second box Ctrl H, of course, under the tab more, we go to special. This time, I'm going to pick up any letter, any letter, not any character, any character means everything, including the space and hyphens and the dots. So any letter is what I'm picking up. It gives me a different code to the power sign and dollar.

And I say replace all. Well, I got all the letters eliminated. Do you want to search the rest of the document? As it asks? Nope. And what I can see is that dots in the spaces so I put a plain and simple.in the Find what box and I say replace all again, no.

And finally, I put a space a blank space and I see replace all. So now the data is in the form as it should look. So what I can do is simply copy this data and go back to excel. Paste it should do a do with a normal paste. But with respect to number especially the ones with starts with z If I simply copy and paste it, the zeros might get eliminated. So what I need to do is before you paste, especially for numbers starting with zero, you must choose the area where you're supposed to paste it.

Go to format cells, and make sure it is stored as text. Yes text. Once you have done so, then yes, you are free to copy the data correct. And do a paste special. And it says pay special as in text. Often it is also called an older version of Word unformatted text.

If I do that, notice the zero gets retain this especially for data with numbers number starting with zero. So this trick has been very, very useful to me because it doesn't require any formula. It simply requires that you copy the data from Excel to word eliminate the numbers or the digit and come back and paste it in Excel. And there you go less than a minute you get all the data sorted in the way that you had wanted. Have you enjoyed it? We'll see more of Crick's upcoming videos.