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.

@excelisfun

4 жыл бұрын

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

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

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

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.

@excelisfun

3 жыл бұрын

Awesome!! You are getting to be so good with Excel ; )

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

Cool, I am glad that it helped!

I am glad that the video helps.

One hell of a formula. It blows my mind ! Mike you are am excel artist

No words to express your contribution

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

That's great!

Yes that is a great VBA solution!

@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

Really nice and universal solution ;-) Thumbs up!

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.

@fahmylina123

4 жыл бұрын

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.

The whole thing went over my head. though nice tutorial.

Thank you very much mate, this formula works like a charm!

Terrific clear explanation that teaches so much more than just this one trick. Thank you.

@excelisfun3903

4 жыл бұрын

You are welcome, Chris!

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

This is amazing,Awesome...! teaching and this was one which i was searching for THANK U

Thank you so much for your help.

Brilliant. Amazing Mike.

@excelisfun

4 жыл бұрын

Glad you like it, Syed MM : ) : ) : ) : )

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.

Awesome! Thank you!

This was beastly. Thanks.

brilliant thank you for ur service to society

@excelisfun

4 жыл бұрын

You are welcome!!!

That is amazing!

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!

Thank you so much. This video is really helpful and it's offer useful explanations.

@excelisfun

6 жыл бұрын

You are welcome, Milica!!!! Tahnks for your support : )

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.

wonderfulll =)) thank you so so much, this is what I'm looking for

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

@excelisfun

4 жыл бұрын

Glad you like the videos, Hardik : )

You are owesome!

cool thanks formula awesome and thank you :)

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?

Amazing : O

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.

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?

@lamenooby , KZread 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 KZread site, but it is THE place to ask questions.

If you have office 2013 you can do this without formulas with FLASH FILL! .... gotta love office excel 2013!!!

Hi I'm looking for to convert date formate 15th August 2008 into 15/08/2008. pls suggest me right string.

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!

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?

how do you show the preliminary results highlighting the formula?

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

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 Mike - Great tutorial! I have a similar, but different situation, and was wondering if you could help? Let's say this is the text string: kdfndk-123.45dkl. I want to extract -123.45 from the text string. Is this possible?

Hi I have improvised this formula to get the reverse of a number. How can I share it with you?

Very interesting

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?

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

My brain is aching.

@kymhunter3418

6 жыл бұрын

Yep and I cant get it to work

@fahmylina123

4 жыл бұрын

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

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

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?

Thank you. 💕

@excelisfun

3 жыл бұрын

You are welcome, Lorena!!!

wow mike 👍🏻👍🏻👍🏻👍🏻

interesting, i am in a problem with huge data where has to extract only text then what to do? pls help me

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.

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

thank i solved it :D

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!

Mike ... Is there a new way to do this with new spill formulas??

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

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?

Thank you sir

@excelisfun

5 жыл бұрын

You are welcome, Ramdayal! Thanks for your support with your comment, thumbs up and Sub : )

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?

outstanding..but how to extract text only from it ?

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

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

@excelisfun

3 жыл бұрын

Enter it into first cell, then copy it.

Try posting your question to THE best Excel site: mrexcel[dot]com I am sure that you will get a great answer.

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

@mohitrd

8 жыл бұрын

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

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 ","

@scgamerd

4 жыл бұрын

Thanks Sorry I answered my question! I used =MID(K2,ROW(INDIRECT("1:"&LEN(K2))),6) and it worked. Miracle! Thanks!

There are only 15 significant digits available in excel.

Awesome

To be honest, this is not my cup of tea. But video is awesome

Domenic from Canada?

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

@excelisfun

10 жыл бұрын

I have never seen that before. I am not sure what is causing it. Try posting question here: mrexcel.com/forum

i have percentage with a text. how can i extract it? only the percent

Thanks again, for amazing formula.

@excelisfun

4 жыл бұрын

Glad the formulas can help, 6642633!!! Thanks for your support with your comments, thumbs ups and of course your Sub : )

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.

@luvlycan

6 жыл бұрын

kamesh sharma =Left(A1,Search(".",A1)-1)

Heyyy !! How do i delete the same words off my own column ?? Sorry for being so rude. but really urgenttt THANKS in advance (:

good

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

@richardendicott9928

7 жыл бұрын

Whenever you hit F9 you need to hit control Z to get back so the executed equation doesn't stay solved

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

I do not know an easier way in 2010.

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

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

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?

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

yes but if string contain 005 that formula will return only 5 :/

how can i find number(175 and 150) from these sentence 1- Top bar @ 175 c/c 2- bottom bar @ 150 c/c Thanks

@martinsger

3 жыл бұрын

IN TIME of pandemy, see here: =MID(B3;(SEARCH("@";B3)+2);3). I'm in Brazil.

How can i download this spreadsheet and follow along?

@excelisfun

10 жыл бұрын

download workbbok: people.highline.edu/mgirvin/excelisfun.htm look for 484-489 in Excel Magic Trick section

I tried substitute formula but no result, and the formula i used very close to yours. Almost same logic

how many of you are here after may june 2019 IT p4

@anoopsinghbhakuni9132

4 жыл бұрын

Hi Iam here

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

@jackonl1244

8 жыл бұрын

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