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

@datauntelligence

2 жыл бұрын

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

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

@datauntelligence

Жыл бұрын

Thanks, I like your comment

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

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

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

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.

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

9 ай бұрын

Same

This is awesome. Thanks for sharing.

@datauntelligence

Жыл бұрын

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

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

11 күн бұрын

Same here

@abhinaykumar3556

11 күн бұрын

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

Thanks for the great help!

@datauntelligence

Жыл бұрын

No worries, thanks for the comment

You are the best. Thank you

@datauntelligence

Жыл бұрын

Ha, thanks

great work mate

What is the formula used in column B2

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

2 жыл бұрын

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

You are amazing!!!Thank you❤

@datauntelligence

3 ай бұрын

Thanks, no worries. Thanks for commenting.

Will this work in MAC Excel?

Great formula to extract number from cell

@datauntelligence

2 жыл бұрын

Thanks, I use this formula a lot.

It misses a decimal point - is there a workaround?

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

@datauntelligence

9 ай бұрын

Nice. Happy it was straightforward.

Thank you I'll try this out

@datauntelligence

7 ай бұрын

Great, it's one of my favourites!

Awesome video

@datauntelligence

2 жыл бұрын

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

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

@datauntelligence

2 жыл бұрын

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

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. kzread.info/dash/bejne/qIec27iOdrbVeNI.html

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

2 жыл бұрын

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

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.

Thanks!

@datauntelligence

29 күн бұрын

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

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

@datauntelligence

7 күн бұрын

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?

Thank You.........

@datauntelligence

11 ай бұрын

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

omg ty a lot

@datauntelligence

Жыл бұрын

No problem. I really like this formula too

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

2 ай бұрын

did you soved it?

@datauntelligence

2 ай бұрын

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

2 ай бұрын

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

2 ай бұрын

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

@josemendoza252

2 ай бұрын

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

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

Thanks

@datauntelligence

Жыл бұрын

No problem, thanks for the comment

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

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. kzread.info/dash/bejne/qIec27iOdrbVeNI.html

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.

@DaddyBearZ

6 ай бұрын

I'm looking for the same. Example: four cells contain 4dog, 6cat, 1dog, 3man. I want a formula that would add 4+6+1+3 (ignoring the words). Is this possible?

Wow

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

@datauntelligence

2 жыл бұрын

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

2 жыл бұрын

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

I tried the formular and got "too many arguments"

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

@datauntelligence

4 ай бұрын

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.

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.

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

@datauntelligence

2 жыл бұрын

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

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

4 ай бұрын

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 😀

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

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

2 жыл бұрын

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

8 ай бұрын

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

@user-ki1df5op8s

3 ай бұрын

I had same issue, this is great work