Text to Columns – Cleaning up Numbers w. Trailing Minus Sign

Advanced Excel Crash Course Section 12: Data Cleaning
2 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€88.46
List Price:  €123.85
You save:  €35.39
£75.37
List Price:  £105.52
You save:  £30.15
CA$138.19
List Price:  CA$193.47
You save:  CA$55.28
A$155.11
List Price:  A$217.17
You save:  A$62.05
S$129.96
List Price:  S$181.95
You save:  S$51.99
HK$774.92
List Price:  HK$1,084.92
You save:  HK$310
CHF 82.69
List Price:  CHF 115.77
You save:  CHF 33.08
NOK kr1,041.08
List Price:  NOK kr1,457.56
You save:  NOK kr416.47
DKK kr660.36
List Price:  DKK kr924.54
You save:  DKK kr264.17
NZ$167.99
List Price:  NZ$235.19
You save:  NZ$67.20
د.إ367.26
List Price:  د.إ514.19
You save:  د.إ146.92
৳12,188.92
List Price:  ৳17,064.98
You save:  ৳4,876.05
₹8,462.20
List Price:  ₹11,847.42
You save:  ₹3,385.22
RM426.95
List Price:  RM597.75
You save:  RM170.80
₦160,354.96
List Price:  ₦224,503.36
You save:  ₦64,148.40
₨28,187.10
List Price:  ₨39,463.07
You save:  ₨11,275.97
฿3,309.66
List Price:  ฿4,633.66
You save:  ฿1,324
₺3,857.27
List Price:  ₺5,400.33
You save:  ₺1,543.06
B$565.91
List Price:  B$792.30
You save:  B$226.38
R1,839.21
List Price:  R2,574.97
You save:  R735.76
Лв172.99
List Price:  Лв242.19
You save:  Лв69.20
₩139,971.25
List Price:  ₩195,965.36
You save:  ₩55,994.10
₪358.69
List Price:  ₪502.19
You save:  ₪143.49
₱5,565.74
List Price:  ₱7,792.26
You save:  ₱2,226.52
¥14,488.55
List Price:  ¥20,284.55
You save:  ¥5,796
MX$1,957.85
List Price:  MX$2,741.07
You save:  MX$783.22
QR364.06
List Price:  QR509.70
You save:  QR145.64
P1,361.73
List Price:  P1,906.48
You save:  P544.74
KSh12,933.70
List Price:  KSh18,107.70
You save:  KSh5,174
E£5,075.98
List Price:  E£7,106.58
You save:  E£2,030.59
ብር13,459.34
List Price:  ብር18,843.61
You save:  ብር5,384.27
Kz91,190.88
List Price:  Kz127,670.88
You save:  Kz36,480
CLP$94,605.53
List Price:  CLP$132,451.53
You save:  CLP$37,846
CN¥729.42
List Price:  CN¥1,021.22
You save:  CN¥291.80
RD$5,902.61
List Price:  RD$8,263.89
You save:  RD$2,361.28
DA13,288
List Price:  DA18,603.74
You save:  DA5,315.73
FJ$225.56
List Price:  FJ$315.80
You save:  FJ$90.23
Q770.30
List Price:  Q1,078.46
You save:  Q308.15
GY$20,924.33
List Price:  GY$29,294.90
You save:  GY$8,370.57
ISK kr12,929.70
List Price:  ISK kr18,102.10
You save:  ISK kr5,172.40
DH926.13
List Price:  DH1,296.62
You save:  DH370.49
L1,709.82
List Price:  L2,393.82
You save:  L683.99
ден5,441.97
List Price:  ден7,618.98
You save:  ден2,177
MOP$801.19
List Price:  MOP$1,121.71
You save:  MOP$320.51
N$1,867.19
List Price:  N$2,614.14
You save:  N$746.95
C$3,690.56
List Price:  C$5,166.94
You save:  C$1,476.37
रु13,561.38
List Price:  रु18,986.48
You save:  रु5,425.09
S/367.72
List Price:  S/514.83
You save:  S/147.10
K405.78
List Price:  K568.11
You save:  K162.33
SAR374.98
List Price:  SAR524.98
You save:  SAR150
ZK2,781.79
List Price:  ZK3,894.63
You save:  ZK1,112.83
L440.50
List Price:  L616.72
You save:  L176.22
Kč2,204.44
List Price:  Kč3,086.31
You save:  Kč881.86
Ft35,802.41
List Price:  Ft50,124.81
You save:  Ft14,322.40
SEK kr965.37
List Price:  SEK kr1,351.56
You save:  SEK kr386.18
ARS$117,263.27
List Price:  ARS$164,173.27
You save:  ARS$46,910
Bs693.19
List Price:  Bs970.49
You save:  Bs277.30
COP$426,824.69
List Price:  COP$597,571.64
You save:  COP$170,746.95
₡50,658.12
List Price:  ₡70,923.39
You save:  ₡20,265.27
L2,602.65
List Price:  L3,643.81
You save:  L1,041.16
₲803,267.16
List Price:  ₲1,124,606.16
You save:  ₲321,338.99
$U4,195.69
List Price:  $U5,874.13
You save:  $U1,678.44
zł378.49
List Price:  zł529.90
You save:  zł151.41
Already have an account? Log In

Transcript

Hello, I'm never satisfied unless I show you all the tips and tricks of text to columns. And that is a reason Here comes another video before you Well, I have a data set where the numbers are ending with a minus sign now, although it is very rare to see these days, but in the old era, in the old er p legacy systems when the data got transported to excel, the credit numbers, the ones which are in negative, this is how it appeared. And you will also notice in some of the credit card statements, the number might end with CRS and the D Rs. So we want to see how to quickly convert this unclean data into a clean format through which you can apply some formula, average formula etc. At this moment, if you intend to apply a sum formula on these couple of numbers, you will not get the correct result.

Why? Because Excel is not able to identify the three numbers where the minus is at the end as proper numbers So what do we do? I'm not going to ask you to write a very elaborate formula which extracts the minus sign and put it at the prefix. No. What I'm going to do is ask you to choose this data, as it is one column, go to Data tab, go to text to column. One side, I do that, I will keep the option as delimited.

Step one of three, I'm not even changing that I go to next, I will ensure that all the checkboxes are turned off just for safety. And in the next, I'm not even doing anything I just need to press finish row in less than a second what I see is the minus has appeared before the number now you must be wondering, what did we do, which made this happen? I'm going to share that trick with you. I showed you the strategy, but I did not tell you the logic behind it. What happens if you go to data and when you go to text to column? Yep, in step three of three, there's a button called our The ones over nobody has explored that so frequently but if you click on advanced notice there's one line item which says trailing minus four negative numbers.

And since it is turned on by default, that is the reason when you press finish the minus becomes first in the prefix. So that was one strategy how to convert the unclean numbers mix of positive and negative numbers into correct positive and negative numbers. In the next video I'll also show how to convert the SI RS and the de RS into proper minus and plus sign till then practice till this point

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.