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
Hi Mike, I tried using aggregate function instead of LARGE and it worked.....Boom.. I have started co-relating various things coz of you.....hats off to you Sir......u r great.God bless you.
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)).
I use this formula regularly to retrieve data from the Weather Sites that always commit the big disadvantage of showing their native weather data (temperature, relative humidity, ...etc) with the units (such as degrees Celsius and %) in the same cell of the numerical value and separated from the numerical value by a space. No one has time to extract such valuable numerical values by hand (this silly task will take centuries, and it is absolutely highly pron to big errors). I usually copy weather basic data tables with (all its silly strings) and then use this great (and rather complex !) formula to extract the needed numerical values to use in data-processing and or in plotting. Thank you the intelligent people at Excel Is Fun (Excel Is Fun), YOU ARE GREAT INDEED.
However, in some weather data table I've observed that the relative humidity string 70% (and the similar strings with zeros, such as 10%, 20%, etc) will appear by this extraction formula as 7 (or 1, 2, etc) instead of 70 (or 10, 20, etc). The simple solution of this silly problem is to type a dash (-) between the numerical value and the % sign (e.g. make 70% as 70-%) in the original weather data table, and the retrieval formula will give you 70 as required.
Thanks Mike exact what I am looking 👍, I was just tired before this formula I need to go through multiple process to extract the numbers now I can one click to go
@lamenooby , RUclips 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 RUclips site, but it is THE place to ask questions.
Hello Mike. Thanks for this tutorial. I have come up with the following much shorter formula. I learnt the formula from you. Please look at the formula and comment. Thanks: 0+TEXTJOIN("",,IFERROR(--MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1),""))
WOW... this was truly amazing... makes me wonder about the greatness of the solution itself and the power of excel as well... all i can say is wonderful... Thank you very much
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.
Tried 9002 different formulas off the net then realised where's my main man Mike Did a quick search "Extract Numbers From String" on his Channel page and "Hey Presto" the man did it again "Solved" Easy Simple solution even i could copy and paste : ) Legend ! Thanks Mike
Thank u so much your formula is amazing... but when am trying to copy paste in more than one cells in Excel... its showing error "You can not change part of an array"... pls suggest what should I do
Dear Mike, I found an easier way to extract only numbers of text in Office 35 to extract only numbers =VALUE(CONCAT(FILTER(MID($A2;SEQUENCE(LEN($A2));1);ISNUMBER(MID($A2;SEQUENCE(LEN($A2));1)+0)))) to extract only text =CONCAT(FILTER(MID($A2;SEQUENCE(LEN($A2));1);NOT(ISNUMBER(MID($A2;SEQUENCE(LEN($A2));1)+0)))) Hope is helpful for some learners of excel.
This is amazing thank you, but when I was trying to “extract” only the numbers from a open form field example -comments field in our system I got the result = ‘1234567+E15’ ;meaning i just got the first numbers on the comenta but not all of them, could you please help?
Very useful formula.... working fine... but if the len of the number is too large it is converting as 0 after 15 digits. can you please tell me how can I fix that... Thank you very much..
FIRST TIME SHOW THIS VIDEO AND CAME OVER YOUR CHANNEL!!! IMPRESSED WITH YOUR KNOWLEDGE OF FORMULA!!! EXCELLENT HIT THE SUBSCRIBE BUTTON FIRST BEFORE WATCHING OTHER VIDEOS!!!!
Thank you. Very detailed tutorial. I have multiple sentences starting with bunch of numbers (i.e. my goal for extracting those #), but I only want to extract first #4-5 positions of the sentence (numbers will ONLY range from 1,000 to 20,000). Where to do I stop building the command? Also numbers are expressed with the "," does that count as a character/#? So for example 2,000=4 positions or 5 positions because there is a ","
This works great... and it almost completely applies to my requirement. But.. if the integer value starts with a 0 and needs to stay, how would you account for that in this formula? I've tried a few things and can't wrap my head around it yet. It currently removes the 0 entirely.
In the event that I didn't want to search through ALL the characters in a given cell for numbers but a select few within the cell. What would I change in the =row(indirect("1:"&len(A6))) equation to say search though the last 5 characters within the cell?
This was fantastic. Do you have a tutorial on removing a certain string of text/numbers from a cell and leaving the rest? For example: A1 (data) = 000001 TABLE TOP 6 PIECE SET 000001 B1 (formula result) = TABLE TOP 6 PIECE SET The string of numbers varies in each row but is always before AND after the portion of the information that I wish to extract from the cell. Using your tutorial, I have extracted the numbers to their own columns (which I also needed to do), but now I need to also pull out the text which unfortunately can contain numbers also... If I could simply remove the leading and following string of numbers I'd be golden. Thanks for your advice!
Hi, thanks for sharing. Your video help me alots!!! But this time it dosen't works for me. there was an error message to say the numbers of cell was too big. the data in the cell was 'Clavulin 500 F Tab 500-125 MG TABLET' , and i want to extract 500-125, how can it works please. Thanks.
Good day I would like to extract the first set of numbers from a list. ie (122,90,84,118.4,128.9) Any ideas on what formula I can use? COIL112X2.5 COIL90X2.5 COIL84X2.0 COIL118.4X1.8 COIL128.9X2.0
The solution given below on the 0 does not work for me (the formula TEXT).Is there any way to replace 0 with another symbol in the string of numbers and letter?
Hello Sir : Can you please let me know if you added any latest video on this " How to extract numbers from text in a cell' ? I use this all the time. Also : I would like to know if you have posted any videos on how to summarize text value in a pivot table without any complex vb scripts. Thank you for your help. Srinivasan
thanks that was great, but i want to use this formula in a more complicated one , (i want to do some operations on the output in the same cell) thanks in advance :)
Hi great tutorial this is what I am looking for but the issue I am having is when I fill down the formula doesnt tranpose accordingly to the cells which I like it to refer to, so for instance my first cell starts from C2 in which I like to use for the remainder I just cannot get it to work, with out going into each formula and changing the cell number manually??? can you help I am using excel 2013
@@kymhunter3418 Use this formula if the target cell is F6 ={SUM(MID(0&F6,LARGE(ISNUMBER(--MID(F6,ROW(INDIRECT("1:"&LEN(F6))),1))*ROW(INDIRECT("1:"&LEN(F6))),ROW(INDIRECT("1:"&LEN(F6))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(F6)))-1))} Just type the simple string 27 C in the cell F7 in your Excel worksheet, and copy this formula to the cell G7 (or any other cell), and the press Ctrl+Shift+Enter (not Enter alone), and it will work fine and give you 27 as it should do.
I entered exactly as you showed, clicked Control_Shift_Enter and I get Excel error: "You have entered too many arguments for this function". Please help!
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
Hi. Excel is truly fun but I have an issue which might not be related to your formula but it happened while I was trying it.. 1) When i hit F9 I can see the result in the formula box, 2) When I hit enter i see 0 as a result in the cell and the formula is still in the formula box. 3) When I am in F9 mode and I hit the enter i get the result displayed in the cell but my formula is replaced by a the calculated value and the formula completely disappears. Thanks, your videos are awesome...
HOw about from Cell A1 containts 234-PC and I wanted to multiply it by the numbers in Cell B1 and put it to Cell C1. How do I do that without using find?
nice!!! but i have one question, how can i extract numbers in an specific situation? for example the text is "the room#19903 in the floor 19" and this formal can only extract 1990319, what if i just want to extract the five numbers or characters after the "#" (so i want to extract "19903" but not the "19", is it possible to do that?
Hi, what if the text has spacebars? Like : "hi you 7098 got" , this formula doesn't work then! edit: Use substitute formula to delete the spaces! New question: What if we want to exclude the number 1 from showing up? Say the text string is HLO56K1? edit: Use a substitute within a substitute to remove 1 !
This works better =SUM(MID(0&A3,LARGE(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))*ROW(INDIRECT("1:"&LEN(A3))),ROW(INDIRECT("1:"&LEN(A3))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A3)))/10) then ctrl+shift+Enter
Hi Mike, am able use this formula but its just giving me the values and not showing the formula when i keep in edit mode, hence am not able to see the formula and drag it down pls help
Hello... Actually I want to contribute a amount in 20 people but answer should be come different for every person in excel. So please would you like to tell me. How can I do?
Very interesting video, thank you Sir. Sir, I have a question. I have 5 line in a cell and 6th line is incomplete. (i.e. "Ram is a good b") I want that after 5th line's full stop(.), the remaining text will remove automatically by any command. I had tried this but it doesn't work. =Left(len(A1)-find(".",A1),A1) Please make a solution.
Sir can you explain what mistake in my formula because it is not working =SUM(MID(0&A3,LARGE(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))*ROW(INDIRECT("1:"&LEN(A3))),ROW(INDIRECT(("1:"&LEN(A3)))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A3)))-1))
Sir please make the video and explain how does it works to extract the numbers. Suppose your string is in A2. =INT(NPV(-0.9,IFERROR(MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)/10,"")))
suppose we got two tables. 1 and 2. and consider i got large data in both but problem is i want the content from table 2 to be found and replaced with content of table one eliminating extra data of table 1 . like example i got ABCDE12345 in table 1 but i want ABCDE of table 2 to be in table 1 and replaces by it eliminating E12345. how m gonna do it? what you call this process and someone suggested to use VB SCRIPT! can you help me out coz i got large data to manage.like i want to search for 1500 words that can match in other table that has data of 15K entries and take the matching 1500 entries and make other table
+Tom Harlow It works on the current sheet but if you copy and paste the code to another Excel worksheet or workbook, after changing it to the correct cell reference, you will still need to press Ctrl+shift+Enter in order for all the numbers to be displayed.
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
This is working in most cases, however, on some occasions for example the cell has 462/002/012 7726163 which returns as 4620020127726160. does anyone know why it is changing the last digit?? It has also changed the last 2 on another 576/094/006 7424636* to 5760940067424640 any help appreciated
You need to hold down ctrl+shift when you are pressing enter since it's an array formula (so in total, its ctrl+shift+enter). If you did it correctly, you should see curled brackets around the formula when you come back and click on the cell.
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
YOU ARE A EXCEL FORMULA GOD MEE.
YOUR SOLVATION FORMULA IS AMAZING.
Glad to help, Auditor KUMAR!!!
I agree, it is one of the cooler and more complicated formulas that I gleaned from the Mr Excel Message Board...
Try posting your question to THE best Excel site:
mrexcel[dot]com
I am sure that you will get a great answer.
No words to express your contribution
One hell of a formula. It blows my mind !
Mike you are am excel artist
Hi Mike, I tried using aggregate function instead of LARGE and it worked.....Boom.. I have started co-relating various things coz of you.....hats off to you Sir......u r great.God bless you.
Awesome!! You are getting to be so good with Excel ; )
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)).
Yes, it is amazing what some of the Excel Experts come up with at the Mr Excel Message Board!
Terrific clear explanation that teaches so much more than just this one trick. Thank you.
You are welcome, Chris!
I am glad that the video helps.
I use this formula regularly to retrieve data from the Weather Sites that always commit the big disadvantage of showing their native weather data (temperature, relative humidity, ...etc) with the units (such as degrees Celsius and %) in the same cell of the numerical value and separated from the numerical value by a space. No one has time to extract such valuable numerical values by hand (this silly task will take centuries, and it is absolutely highly pron to big errors). I usually copy weather basic data tables with (all its silly strings) and then use this great (and rather complex !) formula to extract the needed numerical values to use in data-processing and or in plotting. Thank you the intelligent people at Excel Is Fun (Excel Is Fun), YOU ARE GREAT INDEED.
However, in some weather data table I've observed that the relative humidity string 70% (and the similar strings with zeros, such as 10%, 20%, etc) will appear by this extraction formula as 7 (or 1, 2, etc) instead of 70 (or 10, 20, etc). The simple solution of this silly problem is to type a dash (-) between the numerical value and the % sign (e.g. make 70% as 70-%) in the original weather data table, and the retrieval formula will give you 70 as required.
Thanks Mike exact what I am looking 👍, I was just tired before this formula I need to go through multiple process to extract the numbers now I can one click to go
@lamenooby , RUclips 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 RUclips site, but it is THE place to ask questions.
brilliant thank you for ur service to society
You are welcome!!!
Hello Mike. Thanks for this tutorial. I have come up with the following much shorter formula. I learnt the formula from you. Please look at the formula and comment. Thanks: 0+TEXTJOIN("",,IFERROR(--MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1),""))
Yes, the upper end of Excel is really cool!!
WOW... this was truly amazing... makes me wonder about the greatness of the solution itself and the power of excel as well... all i can say is wonderful... Thank you very much
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.
Thank you very much mate, this formula works like a charm!
Cool, I am glad that it helped!
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!
The whole thing went over my head. though nice tutorial.
@hombreazu1 , You are welcome!
Tried 9002 different formulas off the net then realised where's my main man Mike
Did a quick search "Extract Numbers From String" on his Channel page and "Hey Presto" the man did it again "Solved"
Easy Simple solution even i could copy and paste : )
Legend !
Thanks Mike
It's very useful , But I would like to know reverse. Extract only words(ABCDabcd) from number and word complex. Where can I watch it, plz?
Brilliant. Amazing Mike.
Glad you like it, Syed MM : ) : ) : ) : )
This is amazing,Awesome...! teaching and this was one which i was searching for THANK U
Mike ... Is there a new way to do this with new spill formulas??
Really nice and universal solution ;-) Thumbs up!
Yes that is a great VBA solution!
Thank u so much your formula is amazing... but when am trying to copy paste in more than one cells in Excel... its showing error "You can not change part of an array"... pls suggest what should I do
Enter it into first cell, then copy it.
You are owesome!
To be honest, this is not my cup of tea.
But video is awesome
wonderfulll =)) thank you so so much, this is what I'm looking for
Thank you so much. This video is really helpful and it's offer useful explanations.
You are welcome, Milica!!!! Tahnks for your support : )
Do you know how to extract the 4th digit in an excel date? I need it for a spread sheet I'm working on. Thanks
Thanks again, for amazing formula.
Glad the formulas can help, 6642633!!! Thanks for your support with your comments, thumbs ups and of course your Sub : )
There are only 15 significant digits available in excel.
Dear Mike, I found an easier way to extract only numbers of text in Office 35
to extract only numbers
=VALUE(CONCAT(FILTER(MID($A2;SEQUENCE(LEN($A2));1);ISNUMBER(MID($A2;SEQUENCE(LEN($A2));1)+0))))
to extract only text
=CONCAT(FILTER(MID($A2;SEQUENCE(LEN($A2));1);NOT(ISNUMBER(MID($A2;SEQUENCE(LEN($A2));1)+0))))
Hope is helpful for some learners of excel.
This was beastly. Thanks.
This is amazing thank you, but when I was trying to “extract” only the numbers from a open form field example -comments field in our system I got the result = ‘1234567+E15’ ;meaning i just got the first numbers on the comenta but not all of them, could you please help?
Very useful formula.... working fine... but if the len of the number is too large it is converting as 0 after 15 digits. can you please tell me how can I fix that...
Thank you very much..
I have sheet has column contain text and numbers with no space, how i can use the data in column to search with it in another table?
Thank you so much for your help.
FIRST TIME SHOW THIS VIDEO AND CAME OVER YOUR CHANNEL!!! IMPRESSED WITH YOUR KNOWLEDGE OF FORMULA!!! EXCELLENT HIT THE SUBSCRIBE BUTTON FIRST BEFORE WATCHING OTHER VIDEOS!!!!
Glad you like the videos, Hardik : )
Thank you. Very detailed tutorial. I have multiple sentences starting with bunch of numbers (i.e. my goal for extracting those #), but I only want to extract first #4-5 positions of the sentence (numbers will ONLY range from 1,000 to 20,000). Where to do I stop building the command? Also numbers are expressed with the "," does that count as a character/#? So for example 2,000=4 positions or 5 positions because there is a ","
Thanks Sorry I answered my question! I used =MID(K2,ROW(INDIRECT("1:"&LEN(K2))),6) and it worked. Miracle! Thanks!
If you have office 2013 you can do this without formulas with FLASH FILL! .... gotta love office excel 2013!!!
This works great... and it almost completely applies to my requirement. But.. if the integer value starts with a 0 and needs to stay, how would you account for that in this formula? I've tried a few things and can't wrap my head around it yet. It currently removes the 0 entirely.
In the event that I didn't want to search through ALL the characters in a given cell for numbers but a select few within the cell. What would I change in the =row(indirect("1:"&len(A6))) equation to say search though the last 5 characters within the cell?
This was fantastic. Do you have a tutorial on removing a certain string of text/numbers from a cell and leaving the rest? For example: A1 (data) = 000001 TABLE TOP 6 PIECE SET 000001
B1 (formula result) = TABLE TOP 6 PIECE SET
The string of numbers varies in each row but is always before AND after the portion of the information that I wish to extract from the cell. Using your tutorial, I have extracted the numbers to their own columns (which I also needed to do), but now I need to also pull out the text which unfortunately can contain numbers also... If I could simply remove the leading and following string of numbers I'd be golden. Thanks for your advice!
I have written L= 1+2+3 in A1 and in A2 I want only the result of the sum of (1+2+3)=6 How to do this??
Hi, thanks for sharing. Your video help me alots!!!
But this time it dosen't works for me. there was an error message to say the numbers of cell was too big.
the data in the cell was 'Clavulin 500 F Tab 500-125 MG TABLET' , and i want to extract 500-125, how can it works please.
Thanks.
outstanding..but how to extract text only from it ?
Thank you. 💕
You are welcome, Lorena!!!
Good day
I would like to extract the first set of numbers from a list. ie (122,90,84,118.4,128.9)
Any ideas on what formula I can use?
COIL112X2.5
COIL90X2.5
COIL84X2.0
COIL118.4X1.8
COIL128.9X2.0
The solution given below on the 0 does not work for me (the formula TEXT).Is there any way to replace 0 with another symbol in the string of numbers and letter?
Hi I have improvised this formula to get the reverse of a number. How can I share it with you?
Hello Sir : Can you please let me know if you added any latest video on this " How to extract numbers from text in a cell' ? I use this all the time.
Also : I would like to know if you have posted any videos on how to summarize text value in a pivot table without any complex vb scripts.
Thank you for your help.
Srinivasan
That's great!
interesting, i am in a problem with huge data where has to extract only text then what to do? pls help me
i have percentage with a text. how can i extract it? only the percent
thanks that was great, but i want to use this formula in a more complicated one , (i want to do some operations on the output in the same cell) thanks in advance :)
Hi great tutorial this is what I am looking for but the issue I am having is when I fill down the formula doesnt tranpose accordingly to the cells which I like it to refer to, so for instance my first cell starts from C2 in which I like to use for the remainder I just cannot get it to work, with out going into each formula and changing the cell number manually??? can you help I am using excel 2013
+Paul Bryan
To separate the text string which number is before or after the text, you can use the Flash Fill feature of Excel 2013.
My brain is aching.
Yep and I cant get it to work
@@kymhunter3418 Use this formula if the target cell is F6
={SUM(MID(0&F6,LARGE(ISNUMBER(--MID(F6,ROW(INDIRECT("1:"&LEN(F6))),1))*ROW(INDIRECT("1:"&LEN(F6))),ROW(INDIRECT("1:"&LEN(F6))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(F6)))-1))}
Just type the simple string 27 C in the cell F7 in your Excel worksheet, and copy this formula to the cell G7 (or any other cell), and the press Ctrl+Shift+Enter (not Enter alone), and it will work fine and give you 27 as it should do.
how do you show the preliminary results highlighting the formula?
I entered exactly as you showed, clicked Control_Shift_Enter and I get Excel error: "You have entered too many arguments for this function". Please help!
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
Hi. Excel is truly fun but I have an issue which might not be related to your formula but it happened while I was trying it..
1) When i hit F9 I can see the result in the formula box,
2) When I hit enter i see 0 as a result in the cell and the formula is still in the formula box.
3) When I am in F9 mode and I hit the enter i get the result displayed in the cell but my formula is replaced by a the calculated value and the formula completely disappears.
Thanks, your videos are awesome...
Whenever you hit F9 you need to hit control Z to get back so the executed equation doesn't stay solved
HOw about from Cell A1 containts 234-PC and I wanted to multiply it by the numbers in Cell B1 and put it to Cell C1.
How do I do that without using find?
nice!!! but i have one question, how can i extract numbers in an specific situation? for example the text is "the room#19903 in the floor 19" and this formal can only extract 1990319, what if i just want to extract the five numbers or characters after the "#" (so i want to extract "19903" but not the "19", is it possible to do that?
Thank you this is amazing but what if I want to extract numbers with decimal e.g (kjhda1.5kjc) to be 1.5 thank you :D
how can i find number(175 and 150) from these sentence
1- Top bar @ 175 c/c
2- bottom bar @ 150 c/c
Thanks
IN TIME of pandemy, see here: =MID(B3;(SEARCH("@";B3)+2);3). I'm in Brazil.
Hi, what if the text has spacebars? Like : "hi you 7098 got" , this formula doesn't work then!
edit: Use substitute formula to delete the spaces!
New question: What if we want to exclude the number 1 from showing up? Say the text string is HLO56K1?
edit: Use a substitute within a substitute to remove 1 !
wow mike 👍🏻👍🏻👍🏻👍🏻
That is amazing!
This works better
=SUM(MID(0&A3,LARGE(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))*ROW(INDIRECT("1:"&LEN(A3))),ROW(INDIRECT("1:"&LEN(A3))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A3)))/10)
then ctrl+shift+Enter
Hi Mike, am able use this formula but its just giving me the values and not showing the formula when i keep in edit mode, hence am not able to see the formula and drag it down pls help
I have never seen that before. I am not sure what is causing it. Try posting question here:
mrexcel.com/forum
Thank you sir
You are welcome, Ramdayal! Thanks for your support with your comment, thumbs up and Sub : )
Hello... Actually I want to contribute a amount in 20 people but answer should be come different for every person in excel. So please would you like to tell me. How can I do?
Heyyy !!
How do i delete the same words off my own column ??
Sorry for being so rude.
but really urgenttt
THANKS in advance (:
Very interesting video, thank you Sir.
Sir, I have a question. I have 5 line in a cell and 6th line is incomplete. (i.e. "Ram is a good b") I want that after 5th line's full stop(.), the remaining text will remove automatically by any command.
I had tried this but it doesn't work.
=Left(len(A1)-find(".",A1),A1)
Please make a solution.
kamesh sharma =Left(A1,Search(".",A1)-1)
Sir can you explain what mistake in my formula because it is not working
=SUM(MID(0&A3,LARGE(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))*ROW(INDIRECT("1:"&LEN(A3))),ROW(INDIRECT(("1:"&LEN(A3)))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A3)))-1))
Awesome! Thank you!
Hi I'm looking for to convert date formate
15th August 2008 into 15/08/2008.
pls suggest me right string.
I tried substitute formula but no result, and the formula i used very close to yours. Almost same logic
Very interesting
Sir please make the video and explain how does it works to extract the numbers.
Suppose your string is in A2.
=INT(NPV(-0.9,IFERROR(MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)/10,"")))
Actually am confusing in NPV function how its working in this part
I do not know an easier way in 2010.
cool thanks formula awesome and thank you :)
Domenic from Canada?
suppose we got two tables. 1 and 2. and consider i got large data in both but problem is i want the content from table 2 to be found and replaced with content of table one eliminating extra data of table 1 . like example i got ABCDE12345 in table 1 but i want ABCDE of table 2 to be in table 1 and replaces by it eliminating E12345. how m gonna do it? what you call this process and someone suggested to use VB SCRIPT! can you help me out coz i got large data to manage.like i want to search for 1500 words that can match in other table that has data of 15K entries and take the matching 1500 entries and make other table
Tried this formula... my data is "5-R-C1" and when I use this formula I end up with a return value of "5" instead of "51". What did I do wrong??
+Tom Harlow It works on the current sheet but if you copy and paste the code to another Excel worksheet or workbook, after changing it to the correct cell reference, you will still need to press Ctrl+shift+Enter in order for all the numbers to be displayed.
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
This is working in most cases, however, on some occasions for example the cell has 462/002/012 7726163 which returns as 4620020127726160. does anyone know why it is changing the last digit??
It has also changed the last 2 on another 576/094/006 7424636* to 5760940067424640
any help appreciated
yes but if string contain 005 that formula will return only 5 :/
Awesome
How can i download this spreadsheet and follow along?
download workbbok: people.highline.edu/mgirvin/excelisfun.htm
look for 484-489 in Excel Magic Trick section
i just tried this and after the last sum function when i pressed enter -- the output was ZERO!!! pls help on this
You need to hold down ctrl+shift when you are pressing enter since it's an array formula (so in total, its ctrl+shift+enter). If you did it correctly, you should see curled brackets around the formula when you come back and click on the cell.
how many of you are here after may june 2019 IT p4
Hi Iam here