АЛЬФА КАРАБАС - АРСЕН МАРКАРЯН

Подъёмный кран из полена и доски #diy

НУБ ИЩЕТ ЖЕНУ В GTA SAMP

УГРОЗЫ в ЖИЗНИ из-за Аризона РП 😱⛔️ #аризонарп #ися #самп #гта #shorts

АЛЬФА КАРАБАС - АРСЕН МАРКАРЯН

Excel Magic Trick 1093: Extract Date from Middle of Description: Text To Columns or Formula?

Подписаться

Просмотров 45 тыс.

% 230

Видео Поделиться Скачать Добавить в

Download Excel Start File: people.highline.edu/mgirvin/RU-vidExcelIsFun/EMT1091-1094.xlsx
Download Excel File: people.highline.edu/mgirvin/ExcelIsFun.htm
Extract Date from Middle of Description: Text To Columns or Formula:
1. Text To Columns
2. Formula that uses the functions SUBSTITUTE, REPLACE, SEARCH and LEFT

Опубликовано:

5 апр 2014

Поделиться:

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 59

@excelisfun 10 лет назад

Excel Magic Trick 1093: Extract Date from Middle of Description: Text To Columns or Formula?

@jajayumi8052 Год назад

After long hours of searching on how to extract the date, finally I can do it because of your video. thanks so much!

@excelisfun Год назад

Glad it helps!

@kindz Год назад

WOW !! After so many vids I finally got one that explained/ even touched on the issue I had with sorting Date from a bunch of text & time in the same cell. Thanks a ton!! May the subscription to this channel grow far and wide ..saved me a ton of work! Phew! :)

@excelisfun Год назад

Glad the video helps, Tess!!!

@sharleneglennie3792 5 месяцев назад

oh man. "+0"!!!!!! That there was a massive "of course!!" moment that I wish I'd learned sooner. Genius! thanks

@lilaphoenix2021 6 лет назад

You're like a Excel wizard! Thank you for sharing your magical know-how.

@excelisfun 6 лет назад

You are welcome! Thanks for your support with your comment, Thumbs Up and Sub : )

@ayeshaarshad5955 2 года назад

Oh thank heavens for this video!!

@excelisfun 2 года назад

Glad it helped!!!

@kennethbelarmino2360 9 лет назад

THIS IS SO GENIUS. REAL LIFE SAVER. I LOVE YOU HAHA

@kishoregc490 6 лет назад

Could you help me to find a specific word in sentence with date with in a cell, for example "ABC mail sent on Jan 1st 2017, fgh Mail sent on Jan 4th 2017," here my question is can I have a formal to find only date when is the mail sent to ABC?

@helenblack1179 8 лет назад

I'd like to extract text based on a specific character (/). Example: abcd/efgh/ijkl/delete this text. Since my the text is of variable length, the only consistent character is the last slash. So I want to delete everything that comes after the last slash. What function should I use and how?

@coolkidz33 10 лет назад

Mike-another great video. I have seen you turn off "speak the cells" in several videos. Why do you even have that on in the first place? When would you find that useful to enable?

@excelisfun 10 лет назад

Because I can't type, spell and various other deficiencies. It can catch such things...

@krn14242 10 лет назад

Thanks Mike. Very cool.

@excelisfun 10 лет назад

You are welcome, krn14242!

@MrExcel4u 10 лет назад

Great Trick.....thanks a lot..

@excelisfun 10 лет назад

You are welcome!

@kishoregc490 6 лет назад

Hi is there any date function to capture present date and should not change on next day, for example {=if (a1="gck", today())}, it will capture the date wen I enter the word gck in a1 is Jan 1st 2017, but wen u see next day it will be changed to Jan 2nd 2017, but my requirement is nt to change the date it should remain as Jan 1st 2017 it self, can u help?

@bloggerkickstart9060 11 месяцев назад

Can we use an array formula for this function? I tried it but is not working for me.

@sultanSurya 3 года назад

Thanks fro sharing.. this solution helped me

@excelisfun 3 года назад

Glad it helped!!!

@harshu05khatri 10 лет назад

Why m not able to see most of your videos youtube is showing error message...this has never happened before...

@excelfan85 10 лет назад

Love your work as always. Here is my spin: =(LEFT(REPLACE(A1,1,FIND("-",A1)+1,""),FIND("-",REPLACE(A1,1,FIND("-",A1),""))-2))+0 Thank you again :)

@excelisfun 10 лет назад

Cool! FIND rather than SEARCH!

@mohanpal2737 4 года назад

awesome trick sir

@excelisfun 4 года назад

Glad it is awesome for you, Mohan!!

@oberghaus 10 лет назад

Thanks for the trick with the replace. I usually parse examples like this with MID and a combination of a few FINDs. Assuming the field with the date in between two dashes would be in cell B4, my solution would be: =MID(B4,FIND("-",B4,1)+1,FIND("-",B4,+FIND("-",B4,1)+1)-1-(FIND("-",B4,1)+1)) Works, but for the uninitiated a little bit hard to follow and understand.

@excelisfun 10 лет назад

Great formula! I think the one I did would be hard for the uninitiated also!

@majidsiddique8227 4 года назад

SMART WORKING BOSS

@excelisfun 4 года назад

Glad it is smart for you, MAJID!!!! Thank you for always supporting : ) : )

@royyanti 8 лет назад

what if I don't have pattern?

@excelisfun 8 лет назад

+eva royanti I don't know how to create formulas or code to accomplish a goal with 100% success for anything unless there is a pattern.

@clabzzz 10 лет назад

My formula works specifically for this case since a "4" can be used as a delimiter (Not a generalized formula): =MID(A2,FIND("-",A2)+2,FIND("4",A2)-FIND("-",A2)-1)

@clabzzz 10 лет назад

Not sure why the formatting messed up when I posted it. The ending should read: ....A2) minus FIND("dash",A2)-1)

@excelisfun 10 лет назад

Thanks for the formula!

@inutotoro 5 лет назад

This formula breaks down if any of the dates have a 4 in it. Like any date in April. or 1/14/14. Hard-coding formulas is generally not a good thing.

@BillSzysz1 10 лет назад

Great!!! If we know, how is the max lenght of text before date we can use something like this =0+SUBSTITUTE(MID(SUBSTITUTE(A2,"- ",REPT(" ",99)),99,99)," ","") Thanks for video!!! :))

@excelisfun 10 лет назад

Yes! I love that formul, Bill!!! I have seen this method before and I even stepped through to try and understand it, but this method never "sticks" in my brain. I guess I need to find a new glue!

@BillSzysz1 10 лет назад

ExcelIsFun Long time ago i saw this solution somewhere in internet but i don't remember who is the author of this. But formula below does not has to know how is the max lenght of text before date. (I wrote it a moment ago) =MID(SUBSTITUTE(A2," -"," ",2),FIND("- ",A2)+2,10)+0 Mike, i will never believe that you need a new glue. ;)))) (if i understand correctly your intention)

@BillSzysz1 10 лет назад

Bill Szysz oops....YT change it so... =MID(SUBSTITUTE(A2,"one space and minus","three spaces",2),FIND("minus and one space",A2)+2,10)+0

@excelisfun 10 лет назад

I love your formula: =MID(SUBSTITUTE(A2,"- "," ",2),SEARCH("-",A2)+2,10)+0 WAY COOL!!! New Video for sure! Bill Szysz comes to the rescue again! I love hanging out on our amazing Online Excel Team!

@BillSzysz1 10 лет назад

ExcelIsFun Glad you like it :))) So below is the next one (just for fun with excel) :))) =AGGREGATE(14,6,MID(A2,SEARCH("-",A2)+2,{10,9})+0,1) or =LOOKUP(9E+307,MID(A2,SEARCH("-",A2)+2,{9,10})+0) Greetings!

@kawaljitkaur6 9 лет назад

What if, one needs to extract dates from data set as below:- My date of birth is 07/01/1981 Hmm, 07/01/1981, I think that is when I was born Oh wait, was it 07/01/1981? I think so 07/01/1981 that is when I landed here. Date of birth?!? What is that now? Let me ask my wife, No wait, I think it is 07/01/1981

@bhuprakashsharma6577 6 лет назад

=IFERROR(MID(H2,FIND("/",H2,1)-2,10),"No Date")

Далее

АЛЬФА КАРАБАС - АРСЕН МАРКАРЯН

32:49

АЛЬФА КАРАБАС - АРСЕН МАРКАРЯН

Просмотров 1,6 млн

Подъёмный кран из полена и доски #diy

01:00

Подъёмный кран из полена и доски #diy

Просмотров 110 тыс.

НУБ ИЩЕТ ЖЕНУ В GTA SAMP

22:34

НУБ ИЩЕТ ЖЕНУ В GTA SAMP

Просмотров 286 тыс.

УГРОЗЫ в ЖИЗНИ из-за Аризона РП 😱⛔️ #аризонарп #ися #самп #гта #shorts

00:55

УГРОЗЫ в ЖИЗНИ из-за Аризона РП 😱⛔️ #аризонарп #ися #самп #гта #shorts

Просмотров 197 тыс.

АЛЬФА КАРАБАС - АРСЕН МАРКАРЯН

32:49

АЛЬФА КАРАБАС - АРСЕН МАРКАРЯН

Просмотров 1,6 млн