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))
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?
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. th-cam.com/video/vShyVUFSoHo/w-d-xo.html
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"
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. th-cam.com/video/vShyVUFSoHo/w-d-xo.html
@@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
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.
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
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.
@@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.
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
@@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.
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.
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.
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 😀
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
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.
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)))
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
Same here
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))
This video deserves more videos. Thanks a lot for this Video.
Thanks, I like your comment
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
Same
Thanks!
Thanks legend, every bit helps my small channel. Glad the video was helpful.
does anyone have any idea on how to extract first and second numbers "50+2+7" and add it together into the result.
What is the formula used in column B2
can us remove/reduce the same character in the string?
Will this work in MAC Excel?
It misses a decimal point - is there a workaround?
You are not Just giving the formula but you are explaining it very good man
Thanks for the comment. I try and make the videos short but with explanation too.
in column E shows vertical results but when I tried it's not show in vertical can you resolve
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?
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
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.
th-cam.com/video/vShyVUFSoHo/w-d-xo.html
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"
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.
th-cam.com/video/vShyVUFSoHo/w-d-xo.html
ek cell main koi word kitne times likha hai? kaise pata chalega ?
Great formula...
Thanks for the introduction and simplicity, Plain English..
Next.
Nice. Happy it was straightforward.
Very Good. Thanks.
Nice one. Thanks for the comment.
Wowww, this is just an amazing formula 👏. Thank you 😊
Ha, yes. It has come in very handy for me.
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.
did you soved it?
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
@@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
SOLVED < Just update your excel, even if it looks like it's up to date
Thanks all! Unfortunately I have moved on from that problem, but my guess is that I ended up using VBA or macro.
You are amazing!!!Thank you❤
Thanks, no worries. Thanks for commenting.
This is great - how would you do the opposite and extract letters only?
Thanks. I'm looking to put a video out soon detailing how to extract letters only.
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.
That's great use of combining different formula to get what you need! Glad my video helped with the creation.
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
great work mate
Formula worked great but when I try to use as a lookup value i get an #N/A return. 😞
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.
@@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.
mate your vid is so helpful,
Would it be fancy if you can share the formula's you used for extra and joined numbers
Thanks, I'm glad it was able to help.
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! 🎉
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
@@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.
I just get the #NAME? error when I input that equation...
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.
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.
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 😀
Its not showing me any results. It's a blank cell
This formula might contain elements that can't be used in older versions of Excel.
I tried the formular and got "too many arguments"
Thanks for the great help!
No worries, thanks for the comment
Thank you
I'll try this out
Great, it's one of my favourites!
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
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.
Great formula to extract number from cell
Thanks, I use this formula a lot.
You can avoid using the indirect function by using '=MID(A2,SEQUENCE(1,LEN(A2)),1)' to extract the characters.
Hello, it was great and amazing. Saved lot of time and can support further. Thanks
Amazing formula, but unfortunately it didn't work for me, I'm using the web version maybe that's the problem
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.
This is awesome. Thanks for sharing.
No problem, this formula helps me out all the time.
You are the best. Thank you
Ha, thanks
Thank You.........
No worries. Extracting numbers was a problem for me for a long time. I love using this formula
Awesome video
Thanks, it's a handy trick I use all the time.
Genius!!
Ha, thanks for the comment.
NOT WORKS SHOWS BLANK CELL. HERE IS A FUNC:
=CONCATENATE("",IFERROR(VALUE(MID(F5, ROW(INDIRECT("1:"&LEN(F5))),50)), ""))
TEXTJOIN ALSO CHECKED
Thanks
No problem, thanks for the comment
omg ty a lot
No problem. I really like this formula too
Wow
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)))
Impressive formula!! I love new ways of doing things.
Be careful of the zero that results if there are no numbers in the string.
GREAT
Thank you! I'm using an older version of Excel that does not have the textjoin function.
I had same issue, this is great work