Separating Numbers from a Text – [A Formula Challenge for You]!

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.

Separating numbers from Text

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.

Separate Numbers From Text

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…

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

14 Comments
  1. This could be one of the solutions: =MID(H5,2,LEN(H5)-2)*1000

  2. 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

  3. 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))

  4. Reply
    Krishnaprasad Menon Jul 15, 2016 at 10:59 AM

    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.

  5. 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.

  6. 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).

  7. 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)

Leave a reply

ExcelDemy
Logo