Text to Columns – Cleaning up Numbers w. Leading "Dr/Cr" Text

2 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.60
You save:  A$55.89
List Price:  ৳11,872.36
You save:  ৳3,392.34
List Price:  CA$187.37
You save:  CA$53.54
CHF 91.26
List Price:  CHF 127.77
You save:  CHF 36.51
List Price:  kr884.75
You save:  kr252.80
List Price:  €118.74
You save:  €33.93
List Price:  £107.27
You save:  £30.65
List Price:  HK$1,084.99
You save:  HK$310.02
List Price:  ₹10,502.85
You save:  ₹3,001.03
List Price:  RM586.20
You save:  RM167.50
List Price:  ₦52,986.21
You save:  ₦15,140
List Price:  kr1,265.84
You save:  kr361.69
List Price:  NZ$211.96
You save:  NZ$60.56
List Price:  ₱6,870.27
You save:  ₱1,963.07
List Price:  ₨23,504.32
You save:  ₨6,716
List Price:  S$192.17
You save:  S$54.91
List Price:  ฿4,370.86
You save:  ฿1,248.90
List Price:  ₺1,021.10
You save:  ₺291.76
List Price:  R2,469.09
You save:  R705.50
Already have an account? Log In


Greetings, here comes another challenge and this is in line with the data cleaning strategies that we are trying to pick up using different formulas and techniques. Now, in case you would have noticed some of you may get credit card statements which has car mentioned against the deposits that you have made into your credit card account. Now, if you want to analyze that you would have to convert into a form which Excel understands. So how do you convert the data given on the left column to the column on the right now, we will use two techniques first technique is getting rid of the extra suffix and using the data pressing Ctrl H which is used for Find and Replace, find what I'll say D are replaced with what nothing and eventually what you get is certain numbers which were supposed to be positive, it shows without the Dr.

But we, when I adopt a similar strategy for car, find what car What do I want to replace with? I want to replace the Minus sign, I do that. But imagine if I press the please all where is the minus sign going to appear? Yes, it is going to appear towards the end. So if this is how in few seconds, you have been able to convert the raw data into the next and the final step is you go to data, text to columns. Once you distill their point, go to step two, ensuring there is nothing which has been chosen, all the comma space will not get much of a difference, but better to be safe.

I go to next. And as usual, I'm not clicking on any one of the buttons. Why? Because in the Advanced button, the option of telling minus four negative number is already selected. So I just have to press finish. So essentially, even if you had to do data text to column and assuming there is nothing's chosen in step two, and three, I can simply press finish.

So notice the RCR get converted into a proper number which Excel can relate with Calculate upon

Sign Up