Excel Magic Trick 489: Extract Only Numbers From Text String Array Formula

[hombreazu1]

This was beastly. Thanks.

[ExcelIsFun]

@hombreazu1 , You are welcome!

[inmemblink]

i have problem here ( first of all sry about my possible errors in english) when i type this formula i have an error. When i hit enter it highlight me part of formula where is A6,LARGE at the start of the formula. if anyone knows a solution pls help...thx

[lamenooby]

Heyyy !!

How do i delete the same words off my own column ??

Sorry for being so rude.

but really urgenttt

THANKS in advance (:

[ExcelIsFun]

@lamenooby , YouTube is not a good place to have discussions to get Excel solutions. Try THE best Excel question site:

mrexcel [dot] com/forum

This site is not affiliated with the excelisfun YouTube site, but it is THE place to ask questions.

[oleg8888]

Could you post the actual formula as text here? I can't seam to make it work

[omiuno]

Awesome vid! I have successfully used on one worsheet but on another, It doesn't seem to calculate when I hit cntr+shift+enter. The formula goes to black text and is showing in the cell. But if I hit F9 the correct solution appears. Any thoughts on what I'm doing wrong?

[ExcelIsFun]

The only thing that I can think of is that the calculations is set to Manual. If you set it to Automatic it should work. Excel 2003: Options. Excel 2007 / 2010: Formula Ribbon tab.

[emkandawire]

This does not seem to work if you have decimals e.g <0.2348 JH

[ExcelIsFun]

Try posting your question to THE best Excel site:

mrexcel[dot]com

I am sure that you will get a great answer.

[loyaus]

I learnt this Trick but, when i teach to my friends that they ask me to do this much easier way in 2010.

and How to randomize the words?.

Regarding your valuable answer

[ExcelIsFun]

I do not know an easier way in 2010.

[loyaus]

I have no word to say.

[loyaus]

Hey, you are great.

[ExcelIsFun]

I am glad that the video helps.

[frengal]

hi...this is super useful! really happy to find this! however, one problem i faced. The number of ### characters i need to extract from a cell is about 16 characters. And the answer shows '0' at the last number despite the number being another number. Any idea what i should do?

e.g. actual answer i want = 4444555566667777; But

shown answer = 4444555566667770

[ExcelIsFun]

There are only 15 significant digits available in excel.

[mikeyroro]

Almost forgot about this. If you search through the Excel Hacks book, Hack #28 gives a pretty slick way to grab the numbers...Check it out if you can.

[ExcelIsFun]

Yes that is a great VBA solution!

[08505excel]

Amazing makes me feel like a beginner. Hope MS Excel 2010 incorporated this into a new simple formula. Don't believe there are any text books that explain the capabilities of nested formulas to any fraction of a percent that are demonstrated in your videos. You do a great job of opening eyes to the capabilities of Excel.

Thanks

[ExcelIsFun]

You are welcome!

Because Excel is so infinite, we are all Excel beginners (if we are paying attention)!!

No I have not seen any books that show really complex array formulas. The Mr Excel Message Board is the best place that I know to learn about formulas like this. Occasionally you will see a really specific/ niche Finance or Accounting book that has some big array formulas

[lineber2]

That is amazing!

[ExcelIsFun]

Yes, it is amazing what some of the Excel Experts come up with at the Mr Excel Message Board!

[mikeyroro]

Wow! I have to say, of all the formulas I've learned from you, this one takes the cake. Just amazing...

[ExcelIsFun]

I agree, it is one of the cooler and more complicated formulas that I gleaned from the Mr Excel Message Board...

[hamy72]

Mike, your way of unfolding these megaformulas is truly amazing. These video tutorials are so informative, that it would have taken hours of struggle had it been left to just learn it purely from some textbooks. Watching your videos now, make the process of learning from books and website a breeze! Thanks again!

[ExcelIsFun]

You are welcome!

But I have to say that when I encounter some of the formulas from the MVPs at the Mr Excel Message Board, it takes me hours of examining the different aspects of the formulas until I see the logic of how the formula works. This one took me a while before I could see how it worked.

Really, it does not matter what level of Excel you are at, it usually takes a while to see the logic. But once you do it is like eating candy, or tofu (I like tofu)).

[farouk450]

Wonderful

Strange but true!

[ExcelIsFun]

That is a pretty hard formula, right? But if you know what each formula element does and a bit of math, it works just fine!

[worldwidebug]

this is freaky

[ExcelIsFun]

Yes, the upper end of Excel is really cool!!