Khan Muhammad Kawser
At Udemy, I run several courses on Excel and Data Analysis. In one of my free courses (Excel Formulas and Functions with Excel Formulas Cheat Sheet), Luis, one of the students in the course, asked me an interesting question as shown in the following screenshot.
Luis asked this question in my Excel Formulas and Functions with Excel Formulas Cheat Sheet course at Udemy.
At the end of the question, he displayed sample data. Before proceeding, please download the sample data from this link.
Below, I am showing a part of the data.
You have to separate the number of parts from the texts in the $ Amount column.
$ Amount column is showing numbers in thousands ($48.9K, $2.2K, $7K, and so on). The numbers seem that they are in Number format ( because they are middle aligned; numbers, by default, are placed in the middle of the Excel cells).
But they are not, just check the formatting of the $ Amount column if you have downloaded the data.
Your Challenge
Your challenge is, can you separate the numbers from the texts? And show them in full numbers? Example: $48.9K will be shown as 48900, $2.2K will be shown as 2200, and the numbers will be shown in Number format.
Example: $48.9K will show as 48900, $2.2K will show as 2200, and so on.
What does Luis want?
Luis wants to see the overall money flow in a stock.
In Options trading, Calls are bets that a stock will rise and Puts are bets that a stock will fall.
In simple language, Luis wants to calculate the difference between the Total money invested in Calls Options and Total money invested in Puts Options.
Luis is guessing: If Total Money in Calls Options is higher than the Total Money in Puts Options, the stock should rise and vice versa. Things should not be that straightforward to predict a Stock in Options Trading, but it is obviously a better way than wild guessing when you’re trading Options.
Know more about Options trading from this Investopedia link.
Let’s now focus on the Excel problem, which is the main purpose of this post.
Luis was not able to separate the numbers from the texts in the $ Amount column.
Can you do that?
I have solved this problem using Excel’s SUBSTITUTE(…) function.
To see how I have solved this problem, download this worksheet.
Can you solve this problem in a better way? Try and post the solution in the comment box.
Master Excel Formulas & Functions in Just 3.5 Hours!
with my FREE COURSE at Udemy.
Excel Formulas and Functions with Excel Formulas Cheat Sheet!
Read More…
- Creating Email Addresses from a Single Column with Flash Fill, TEXT Formulas & Commentator’s Text Formula Suggestions
- Excel Array Formula Basic – What is an Array in Excel?
- How to Separate Words in Excel Using Formula (Ultimate Guide)
- Separate, Split & Remove Substring & Number from Text with Excel Functions & VBA
This could be one of the solutions: =MID(H5,2,LEN(H5)-2)*1000
Cool 😀
Since the K is part of the cell contents (not formatting) I did it with
=LEFT(H5,LEN(H5)-1)*1000
A VBA solution would be to use the below function:
Function GetNumbers(Cell As Range) As Long
‘Pulls just the numbers from a cell
Dim LenStr As Long
For LenStr = 1 To Len(Cell)
Select Case Asc(Mid(Cell, LenStr, 1))
Case 48 To 57
GetNumbers = GetNumbers & Mid(Cell, LenStr, 1)
End Select
Next
End Function
Thanks for the input, specially for the VBA part!
Actually, he said that some numbers might have a “M” for millions, so the correct function would be something like this:
=IF(RIGHT(H5,1)=”K”,LEFT(H5,LEN(H5)-1)*1000,IF(RIGHT(H5,1)=”M”,LEFT(H5,LEN(H5)-1)*1000000,0))
Great Answer!
In the given question, the currency was $ and your formula worked. If the currency changed into Euro or Pound, it will give an error. Hence the common solutions shall be
=MID(H5,2,(LEN(H5)-2))*1000 (where as H5 is the 1st cell in your data sheet)
Krishnaprasad Menon, India.
Yes, Menon! It’s a nice solution and more generic!
Thought I’d add my two pence worth. I generally try and use a VBA solution and error check as much as possible so that no errors are left in the cell contents. First I’d get the middle section of the value in the cell and then if the first character of the value is a monetary symbol and the middle section is a number, I test the last character.
If that is a “K”, multiply the middle section by one thousand.
If it is an “M”, multiply the middle section by one million
If it is a number then the result should be the whole cell value except for the monetary symbol (assuming that there can be values less than 1K and that they have no indicating suffix) and anything else is a zero. If the middle section is not a number or the first character is not a monetary symbol, that also results in a zero.
Function ConvertAmount(TheCell As Range)
Dim MidSection As Variant
Dim LastChar As Variant
With TheCell
MidSection = Mid(.Value, 2, Len(.Value) – 2)
LastChar = UCase(Right(.Value, 1))
If Left(.Value, 1) Like “[$£€¥]” _
And IsNumeric(MidSection) Then
Select Case True
Case LastChar = “K”
ConvertAmount = MidSection * 1000
Case LastChar = “M”
ConvertAmount = MidSection * 1000000
Case IsNumeric(LastChar)
ConvertAmount = MidSection & LastChar
Case Else
ConvertAmount = 0
End Select
Else
ConvertAmount = 0
End If
End With
End Function
This code does not handle negative figures as there is no mention of them made in the challenge.
First Rate Work, Kadeo! Take my bow 😀
I agree with Krishnaprasad – we must handle both K and M to solve the problem as stated. I propose the following, which executes a little faster than using IF statements:
= (LEFT(H6,LEN(H6)-1))*10^ ((RIGHT(H6,1)=”K”)*3 + (RIGHT(H6,1)=”M”)*6)
People don’t always do as we expect, so the following solution handles the case in which someone just enters the number without a K or M suffix:
= (LEFT(H6,LEN(H6)-INT(CODE(RIGHT(H6,1))/65)))*10^ ((RIGHT(H6,1)=”K”)*3 + (RIGHT(H6,1)=”M”)*6)
The addition to the formula captures the whole number if the last character is 0 through 9 and all except the final character if it is not.
INT(CODE(RIGHT(H6,1))/65) is zero for characters 0-9 and 1 for A-Z (and a-z).
Mike, you did a phenomenal job! Thinking in that way, awesome and the alternative!; Hats Off!
I have chosen CHOOSE function() and the formula would be
‘=VALUE(MID(H5,2,LEN(TRIM(H5))-2))*CHOOSE(IF(FIND(“K”,H5)>0,1,IF(FIND(“M”,H5)>0,2,IF(FIND(“B”,H5)>0,3))),1000,100000,1000000)
Thanks, Mohan!