number

Extract Numbers from a String in Excel | Using Formula and VBA



Sharing buttons:

hello and welcome to the video by Trump

Excel I am so mad Mansell and in this

video I will show you how to extract the

numeric part or the text part from a

string in Excel so here I have these

four sentences these four sentences have

the text characters as well as the

numeric characters and here I would

extract the numeric part as well as the

text part now to do this you need to be

using Excel 2016 because there is a new

function in 2016

which is the text join function and if

you're using Prior versions you will not

have access to this function and in that

case I'll show you how to use VBA to do

this but if you are using Excel 2016

then you can use this function now it

will be a little complex function so let

me create that function explain every

part of it the first part of the

function is indirect function so here I

would construct this function oops and

let's see what this function would

return so what I've done is indirect one

colon and then I have used the length of

this string and if I select this and

press f9 it returns 19 this means that

this indirect function would refer to 1

colon 19 that is row number 1 to row

number 19 if I press f9 you can see it

refers to indirect 1 : 19 and now when I

add the row function here and now I

select this entire formula and press f9

you can see it gives me an array of

numbers which is 1 2 19 where 19 is the

length of this characters which means

that it will change for each of these

texts these sentences now let me press

ctrl-z to go back now with this row

formula I would use the mid function and

mid function gives me the specified

number of characters so in this case I

want to use this sentence this text here

and I want the start number to be this

array which means I want the start

number first to be 1 then 2 is 2 then to

be 3

up to 19 and I want to extract only one

character every time so when I select

this function and press f9 it will give

me all these characters separately so

you can see I have T then H then e then

a space then C OST all these characters

are here but now I can analyze these

characters separately let me press

ctrl-z and now with this mid function

all I need to do is I need to check

whether it's a text or a number so what

I would do is I would come here and I

would multiply this by 1 and see what

happens

now when I select this entire thing and

press f9 it gives me value error

wherever there is a text character but

if it is a number it will give me that

number so you can see that the last

three characters are 1 0 0 but

everything else is a value error so what

I can do is I would come here and I

would say if error and if error if this

is the value and if you find an error a

return a blank else return the character

so what this would do is if I select

this entire formula and press f9 you can

see it removes all the text characters

because those were returning value

errors and it keeps those numbers

because these numbers were not giving me

the error now all I need to do is use

the use the text joint function here so

in text joint function it takes three

characters the first one is a delimiter

so the delimiter here would be blank I

don't want any delimiter the second one

here would be true because I don't want

to consider empty cells here so this

would be true and the third here would

be this array which I want to combine

which I want to join and now when I

press control shift enter it gives me

the numeric part and if I copied for all

these cells it will give me the number

for all these sentences all these text

rings and you can see it has extracted

only the numeric part remember to use

ctrl shift

if you just ship use enter it will not

give you the right result you will have

to use control shift enter because the

mid function here is giving us an array

and because it's an array you need to

use control shift enter now another

thing you need to know is that it will

combine all the numerals all the numbers

and put it together so for example if I

come here and I say the ticket price of

10 tickets is USA 25 and I press enter

here you can see it will give me 10 and

25 these numbers together because it it

extracts these numbers and joins these

numbers so let me press ctrl-z

it makes sense to use this formula only

if you have one instance of the number

or when you are fine by having all these

numbers combined it's just a limitation

that you need to know now when we come

to the text part of this function I'll

copy this entire mid function and for

text part we'll have to modify this

function of it so in this case this

entire part of the formula would give us

value errors whenever there is a text

character and a number when there is the

number here so let me press ctrl-z

and what I would do here is I would use

this with is error function so what is

Error would do is it would convert these

into trues and falses if i press f9 you

can see whenever there is a text

character it returns true but whenever

there is a number it gives me false so I

press ctrl-z and now I would use this

with an if formula if there is a true

give me the value and if it is a false

then I will give blank so within this if

formula I would simply use the same mid

function so if there is a true give me

the character from this and if it is

false then give me blank and now let's

see what happens if I select this entire

formula and press f9 it gives me the

text characters but in case of the

numbers it will give me blank now I will

press ctrl-z and here I'll come and I'll

use

the text join function the delimiter

would be blank this would be true and

now when I press control shift enter it

gives me the text part here so you can

see it gives me the cost of cost is USD

and the number doesn't appear so in case

you want to extract the text part you

can use this function if you want to

extract the numeric part you can use

this part now if you're using Excel 2013

or prior version then you will not have

the text join function in that case you

can use a simple VBA code to do this so

let me duplicate this sheet and I'll

remove these formulas what I will do

here is I would create two formulas want

to get the numeric part and want to get

the text part so two let's first create

the numeric one I would go to the

Developer tab and here I would go to

visual basic you can also use the

keyboard shortcut alt f11 and here you

can see I have this project Explorer

where I have all these objects since I

have two sheets those two sheets are

open here what I need to do is

right-click on any of these objects go

to insert and insert a module and in

this module code window we will have to

write the function so the code here

would be function and let's call the

function get numeric and it will only

take one argument which would be the

cell reference will it call it cell

references this cell and the contents of

this cell now let's create this function

the first thing I would say is them

string length as integer the second

thing I would do is string length oops

is equal to Len of cell ref so this

string length would now have the number

of characters in each cell so if I refer

to this cell then it will give me 9 p.m.

because that is the number of characters

in the cell

and now I would use a simple for loop

and I would say for I is equal to one

two string length if is numeric and is

numeric is a function that will tell us

if a character is numeric or not and I

would say mid cell ref and start number

would be I and I only want one character

here and if this is the case then result

is equal to result and the same thing

which is I can copy this part instead of

typing it again I can simply copy it

here and I will explain what each of

these steps does and then I can say next

I and then I can come here and I can say

get numeric is equal to which is the

value that the function the final output

should get would be the value of a

result now in this case you can also

come and initialize these characters

which is let's say Tim I as integer Tim

result as maybe long or maybe you can

come here and you can say string or if

it's numeral you can say numeric but I

would not use anything because I'll

leave it as it is now let's see how this

function would work this is the function

let me quickly first go through this

function what it does is it leave first

get the length of the text here in this

cell once it has the length it will use

the for loop for as many characters as

many characters are here so in this case

since there are 19 characters this loop

for go for 19 times and every time it we

analyze each of these characters one by

one so first time it will analyze T and

where it analyzes T it will see that p

is not numeric so is numeric would be

false and then nothing would happen it

will go to the next one which would be

then H

and then it comes here to the third last

one which is one it will say yes this is

a numeric character so it will simply

add it to the result and when it comes

to zero it will add be added to the

result so it would be 1 then 0 then 0

and then finally the value of the result

which would be hundred would be

transferred to get numeric yes let's see

how it works I would come here and now I

can use this function you will see that

it appears here there would be no

intellisense you will have no no help

here in this case but since we are only

using one cell reference one argument we

know it will be this one and now when I

hit enter

oops so I need to define this variable

what I'll do is I would say the reason I

need to do this is because I've used

option explicit here so one thing I can

do here is if I don't want to define it

maybe I can so dim result as long and

one thing I can do is I can remove

option explicit so I do not have to

define these characters else the better

way is to simply define it then now let

me close it come back here and hit enter

and you can see it Rizza it returns 100

now if I copied for all these cells you

can see I have the number part of from

all these strings here using the get

numeric function now let me press ctrl

f11

sorry called f11 to go back and now I

would create another function which does

the same thing but instead of numbers

it'll extract text so in this case let's

make it get text everything is the same

but here we'll simply add a small part

which is not so if it is a number this

will be false if it is not a number then

this would be true and everything else

remains the same and here we would make

it get text and in result I can make it

string now let's come back and let's try

this function here again I can try

type get text

is the only cell reference the only

argument it takes and now when I hit

enter it gives me the text part from

this sentence so if you are not using

Excel 2016 and you do not or you do not

want to use the text joint function

because it's a bit complicated and

you're more comfortable with VBA then it

is a quick way to simply use this code

to create a function that would extract

the numeric part from a text ring or the

text part from a text string using VBA

custom functions so this is how you can

get only the numeric or the text part

from a string in Excel I hope you found

this video useful thank you and have a

nice day