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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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! 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 a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

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

• Reply Cool 😀

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

• Reply Thanks for the input, specially for the VBA part!

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

• Reply 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)

• Reply Yes, Menon! It’s a nice solution and more generic!

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

• Reply First Rate Work, Kadeo! Take my bow 😀

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

• Reply Mike, you did a phenomenal job! Thinking in that way, awesome and the alternative!; Hats Off!

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

• Reply Thanks, Mohan! Advanced Excel Exercises with Solutions PDF  