Get NUMBERS only from string in EXCEL

Learn how to extract only the numbers from a cell in excel.
www.datauntell...
Formula Data File available for download here: www.etsy.com/a...
Get all the numbers, regardless of where they appear in a cell.
File location :
www.etsy.com/a...
REMOVE NUMBERS & EXTRACT TEXT video:
• Remove Numbers - Extra...
The EXCEL - EXTRACT series playlist
• EXCEL - Extract ANY wo...
2 MINUTE EXCEL FORMULA TUTORIALS
=XLOOKUP Basics - • EXCEL - XLOOKUP... For...
=IF(Contains) - • Excel If formula - If ...
=(Get First Word) - • Get first word or get ...
=(Get Last Word) - • Get last word in Excel...
=(add months) - • Add Months & Years in ...
=(count if) - • Countif formula in Exc...
=(rank unique) - • Rank Formula in Excel ...
=Multiple If Statements - • Excel IF function Mult...
=Sumif and Sumifs - • Learn the Sumifs & Sum...
=Countif and Countifs - • Countif formula in Exc...
PIVOT TABLE TUTORIALS
=PIVOT TABLE - The Very Basics - • Introduction to PIVOT ...
=PIVOT TABLE - Format Dates - • How to change date for...
=PIVOT TABLE - Calculated Fields - • Excel Pivot Table Grow...
=PIVOT TABLE - show fields with no data - • Show all fields in Piv...
=PIVOT TABLE - replace blank cells - • Excel Pivot Tables - F...
#excelformulas
#extractnumbers

[@debbiecarmody3279]

Impressed on how readily this was broken down. Incredibly easy to understand. Thank you.

[@datauntelligence]

Thanks for the feedback. Glad this came in handy for you.

[@shashi0184]

This video deserves more videos. Thanks a lot for this Video.

[@datauntelligence]

Thanks, I like your comment

[@nomadtales_11]

You are not Just giving the formula but you are explaining it very good man

[@datauntelligence]

Thanks for the comment. I try and make the videos short but with explanation too.

[@ariqsusanto3190]

Hi, its a really helpful video, but when i used the 2nd formula its only show number 1 in the cell, did you know why? Thank you

[@mirza1509]

Same

[@atropolifilm4500]

Your formula is amazing because it has many applications. There is no formula in sheets or excel to my knowledge allowing you to search for identical values in two different value tables. With few adjustments, your formula can be used for that. I developed a similiar formula 4 weeks ago because there simply was no formula I could find that automatically finds shared values between two different sized value sets.

[@datauntelligence]

That's great use of combining different formula to get what you need! Glad my video helped with the creation.

[@MarcusBerube-f3f]

Not sure if anyone can help: When i do this, it doesn't fill the rest of the cells 2-8. It seems it is only looking at the first character

[@abhinaykumar3556]

Same here

[@abhinaykumar3556]

In excel, use this - =MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) or sheet convert this into arrayformula -=ArrayFormula(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))

[@AlejandroGonzalez-bj2gh]

Thanks!

[@datauntelligence]

Thanks legend, every bit helps my small channel. Glad the video was helpful.

[@jamesantonywhitehead2814]

Great formula... Thanks for the introduction and simplicity, Plain English.. Next.

[@datauntelligence]

Nice. Happy it was straightforward.

[@nazarkamal8831]

Really this one challenging for me !!! So I subscribed your channel!! Every video I see about Xcel I use to execute successfully by seeing only one time !!!! But this one I saw it for many time !!! But still couldn't able to execute correctly!!! Gonna try again 2morrow

[@datauntelligence]

Hi, thanks for subscribing. This is a pretty complex formula and I hope you can master it soon. Just be careful to make sure you put everything in the formula carefully. Please note that some older versions of Excel might not have the capability to execute the formula.

[@tshara9121]

Wowww, this is just an amazing formula 👏. Thank you 😊

[@datauntelligence]

Ha, yes. It has come in very handy for me.

[@AlinaCarpiuc]

You are amazing!!!Thank you❤

[@datauntelligence]

Thanks, no worries. Thanks for commenting.

[@teengtoong8223]

Very Good. Thanks.

[@datauntelligence]

Nice one. Thanks for the comment.

[@Aussifishtanks]

great work mate

[@Sandeepkol3]

How to extract date from the following... Pl advise AN/24-25/713 dt. 5.12.24 UPNO/0367/2425 dt. 10.12.24 AHSWB/24-24/22 dt. 03.12.24

[@datauntelligence]

Hi there. Did you end up finding a solution. As it appears that your dates are at the end of each line, you could use the textafter function, to get everything after the last space in the cell. Check out this video that shows you how to get the last word from a cell. kzbin.info/www/bejne/Z4SYdYeBf7RoapI

[@tusharshivan9559]

does anyone have any idea on how to extract first and second numbers "50+2+7" and add it together into the result.

[@yyusof78]

Hello, marvelous formula. I've a question. How do I extract the number "22" from "LTM-22-6755" and change the "22" to "2022". "LTM-22-6755" --> "22" --> "2022"

[@datauntelligence]

Hi there. Your example looks like you are trying to extract the second word or part of a string, but instead of spaces separating the text, it is a dash instead. Check out this video that shows you how to extract the 2nd word from a string, but just remember to replace the space " ", with a dash "-" in the formula. kzbin.info/www/bejne/rITLqomLe7iiftE

[@vinaysinghal5721]

Hello, it was great and amazing. Saved lot of time and can support further. Thanks

[@greenwouddeluxe]

Thank you I'll try this out

[@datauntelligence]

Great, it's one of my favourites!

[@tiptoppets3467]

Lovely, how do I extract specific numbers from a Sku. For example Boots-4437-14.97-29.08-8-DS I will always need the third sequence: 14.97

[@datauntelligence]

Hey, your situation looks very similar to extracting the 3rd word, but instead of 'spaces', you sequence is separated by 'dashes'. Check out this video, but when it comes to the formula, replace the space " " with a dash "-" Hope that helps. kzbin.info/www/bejne/rITLqomLe7iiftE

[@edsonfirmino2402]

Thanks for the great help!

[@datauntelligence]

No worries, thanks for the comment

[@rajvarma6438]

Great formula to extract number from cell

[@datauntelligence]

Thanks, I use this formula a lot.

[@theoyiorkas]

If i have a column with text and i want to extract numbers from text and sum up the values in one formula? Is it possible? Well done! 🎉

[@datauntelligence]

Hi, if you use my formula and want to convert the numbers from text format to number format, you can wrap my formula in brackets and then multiply by 1... =(____)*1

[@theoyiorkas]

@@datauntelligence Perhaps I did not explain my question well. I have text from A1 to A15. Below I want to extract the numbers from each cell and find the sum directly with a single formula. Can this be done with your formula? I tried putting it all in parentheses and before writing SUM but I get #VALUE.

[@febiefebriansyah]

can us remove/reduce the same character in the string?

[@AccountsAsclepius]

in column E shows vertical results but when I tried it's not show in vertical can you resolve

[@datauntelligence]

It is using 'spill' formula which isn't available in older versions of excel. Could this be the issue? Or maybe you have something in the cells below already?

[@kennethrandygarcia8786]

mate your vid is so helpful, Would it be fancy if you can share the formula's you used for extra and joined numbers

[@datauntelligence]

Thanks, I'm glad it was able to help.

[@nsanerydah]

Genius!!

[@datauntelligence]

Ha, thanks for the comment.

[@akkibaatality6631]

What is the formula used in column B2

[@mohammedishtiyaqahmed3393]

This is awesome. Thanks for sharing.

[@datauntelligence]

No problem, this formula helps me out all the time.

[@josemendoza252]

Hey, thanks for the video.. for some reason my excel worksheet is not listing out the different rows 1-8 which is not letting the formula work.

[@metrix_x]

did you soved it?

[@datauntelligence]

Hey there, it might be that you are using an older version of excel. This types of 'SPILL' haven't always been available in excel

[@metrix_x]

​@@datauntelligence Thanks, but my excel is at Version 2301, which was released in 2023, later than the video was posted If I figure it out I'll come back

[@metrix_x]

SOLVED < Just update your excel, even if it looks like it's up to date

[@josemendoza252]

Thanks all! Unfortunately I have moved on from that problem, but my guess is that I ended up using VBA or macro.

[@toninolo8579]

You are the best. Thank you

[@datauntelligence]

Ha, thanks

[@mrmikeegan]

This is great - how would you do the opposite and extract letters only?

[@datauntelligence]

Thanks. I'm looking to put a video out soon detailing how to extract letters only.

[@ravikant540]

Sir i have a sentence. e.g. "swp No. 1001/2002 name Harsh v/s you & Ors". I want to extract only 1001/2002 in this sentence. Is there any formula i can use. Please intimate. Regards

[@RajshekherGarikapati]

It misses a decimal point - is there a workaround?

[@funteav3437]

Will this work in MAC Excel?

[@joywithjaniandjoel]

Sorry to say.. This formula is not working. Can you let me know if any other point missed to mention in this video, because when i try to replicate this the formula does fill the remaining cells.

[@datauntelligence]

That's a shame. Sorry to hear it's not working for you. There are some older versions of Excel where it might not work. Otherwise, as you can see in the video, it works a treat 😀

[@andrevanroy3099]

You can avoid using the indirect function by using '=MID(A2,SEQUENCE(1,LEN(A2)),1)' to extract the characters.

[@mohamedfarzan6668]

Thank You.........

[@datauntelligence]

No worries. Extracting numbers was a problem for me for a long time. I love using this formula

[@danvel524]

Awesome video

[@datauntelligence]

Thanks, it's a handy trick I use all the time.

[@j23chtwn]

Formula worked great but when I try to use as a lookup value i get an #N/A return. 😞

[@datauntelligence]

Glad it helped. In regards to the #N/A from your lookup, it would most likely be due to the format. As these numbers were extracted from text, the resulting number would also be formatted as text. The easiest way to change it to a number format is to add *1 to the end of the formula. This will multiple the text value by 1, and turn it into a number format.

[@atropolifilm4500]

@@datauntelligence Thanks! This was the exact problem I had with my current formula. I only needed the info you gave in this comment, saving hours of watching videos to find the solution for such a small problem. On a side note: Your formula is amazing because it has many applications. There is no formula in sheets or excel to my knowledge allowing you to search for identical values in two different value tables. With few adjustments, your formula can be used for that. I developed a similiar formula 4 weeks ago because there simply was no formula I could find that automatically finds shared values between two different sized value sets.

[@rinojunior5238]

Amazing formula, but unfortunately it didn't work for me, I'm using the web version maybe that's the problem

[@datauntelligence]

Thanks, it's a great way to extract just the numbers from a cell. It was built with Excel365 and I know some earlier editions don't work as well.

[@А.Цогт-ЭрдэнэБэсүд]

omg ty a lot

[@datauntelligence]

No problem. I really like this formula too

[@SSIcely]

I just get the #NAME? error when I input that equation...

[@datauntelligence]

Hi there, it is a long formula, so hopefully there isn't a comma or something else missing. You might also be using an older version of Excel where this type of formula is not available.

[@TOP--lb1rc]

NOT WORKS SHOWS BLANK CELL. HERE IS A FUNC: =CONCATENATE("",IFERROR(VALUE(MID(F5, ROW(INDIRECT("1:"&LEN(F5))),50)), "")) TEXTJOIN ALSO CHECKED

[@Swac007]

Nicely done, though it was giving me errors. Try this formula. =INT(TRIM(SUMPRODUCT(MID(0&G1,LARGE(INDEX(ISNUMBER(--MID(G1,ROW(INDIRECT("1:"&LEN(G1))),1))*ROW(INDIRECT("1:"&LEN(G1))),0),ROW(INDIRECT("1:"&LEN(G1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(G1)))/10)))

[@datauntelligence]

Impressive formula!! I love new ways of doing things. Be careful of the zero that results if there are no numbers in the string.

[@manjulaheenkenda1291]

GREAT

[@ThePapaDawg]

Thank you! I'm using an older version of Excel that does not have the textjoin function.

[@Aussifishtanks]

I had same issue, this is great work

[@mukeshkumarkashyap9157]

ek cell main koi word kitne times likha hai? kaise pata chalega ?

[@amitbhatnagar2053]

Its not showing me any results. It's a blank cell

[@datauntelligence]

This formula might contain elements that can't be used in older versions of Excel.

[@dennismarin2351]

I tried the formular and got "too many arguments"

[@abdulrahmanmuslih141]

Thanks

[@datauntelligence]

No problem, thanks for the comment

[@asmrindia]

Wow