Get NUMBERS only from string in EXCEL

[@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

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

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

[@jamesantonywhitehead2814]

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

[@datauntelligence]

Nice. Happy it was straightforward.

[@tshara9121]

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

[@datauntelligence]

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

[@tusharshivan9559]

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

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

[@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

[@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

[@febiefebriansyah]

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

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

[@teengtoong8223]

Very Good. Thanks.

[@datauntelligence]

Nice one. Thanks for the comment.

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

[@Aussifishtanks]

great work mate

[@AlinaCarpiuc]

You are amazing!!!Thank you❤

[@datauntelligence]

Thanks, no worries. Thanks for commenting.

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

[@akkibaatality6631]

What is the formula used in column B2

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

[@vinaysinghal5721]

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

[@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?

[@funteav3437]

Will this work in MAC Excel?

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

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

[@RajshekherGarikapati]

It misses a decimal point - is there a workaround?

[@greenwouddeluxe]

Thank you I'll try this out

[@datauntelligence]

Great, it's one of my favourites!

[@mohammedishtiyaqahmed3393]

This is awesome. Thanks for sharing.

[@datauntelligence]

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

[@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

[@toninolo8579]

You are the best. Thank you

[@datauntelligence]

Ha, thanks

[@nsanerydah]

Genius!!

[@datauntelligence]

Ha, thanks for the comment.

[@mukeshkumarkashyap9157]

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

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

[@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 😀

[@danvel524]

Awesome video

[@datauntelligence]

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

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

[@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

[@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

[@abdulrahmanmuslih141]

Thanks

[@datauntelligence]

No problem, thanks for the comment

[@dennismarin2351]

I tried the formular and got "too many arguments"

[@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

[@asmrindia]

Wow

[@stephenwachira3306]

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