Great simple UDF to get the job done.. excellent! Here is my version.. tinkered with it a bit.. just for the practice. It gives the user some in-Cell feedback if wrong delimiter or word position out of range: Function Extract_Text(Search As String, Delim As String, Position As Integer) Dim arr() As String arr = Split(Search, Delim) If Position < 1 Or Position > UBound(arr) + 1 Or InStr(Search, Delim) = 0 Then Extract_Text = "ERROR! - Syntax is: Extract_Text(Search, ""Delim"", Position)" Else Extract_Text = arr(Position - 1) End If End Function Thanks for the inspiration to create. Thumbs up!
You're very welcome and thanks for watching! In your udf, I Love the idea of outputting the correct syntax along with the error in a udf!!!! I never thought of that and it really is a great way to make sure a user is aware of the syntax. Also, it's great to see different ways to do the same thing.)
How do you extract a string from another string, such as 'bbb' from unstructured strings 12bbb4ty and 1w3bbbrt5? We need to match that somehow and then take it out and this is the regex kind of function. Is something like that an option in Excel? Either way, thanks for help 🙏!
how can i extract keywords and create a frequency distribution table in descending order, if I have thousands of data and lets say 4-5 different keywords
how to extract letter form a word or number? for example the word is "Phenomenon " & I need to extract forth letter from this word that is "N" is this possible in excel??????????????
I'm needing to create customer ID using first 3 letters of name and 3 letters of last name as a customer id.. example John Murphy = johmur How can I do that?
Not really - you need a macro for this that will search the entire spreadsheet, identify an email address and then copy/paste it somewhere. This is a bit of a process. You can ask for help doing this on our forum or paid help via the Contact section if you like - www.teachexcel.com
This is amazing, thank you. Anyway to stop #Value! When there is no word. So get word on 4th occurance but there is no word so it returns #Value!. Some sentences are 5 words long, some only 3.
Hi. I have to extract 15 different words from 5 thousands cells "with different sentences" "in different order". Each cell contains one or two of these 15 words. Therefore your formula doesn't work as each cells are very different. Is there ar formula that can extract the mach word(s)? For example, if the 15 words are, apple, orange, lemon, etc, a formula like =Get_word(cell, "apple", "orange",,,,) and if matches one word, just leave it like "apple".
Sir, I request you to please make a formula in which I can extract two or three words from the middle of the line of a cell in Excel. I have made different formulas for both, but I am not able to form a formula by mixing them both. I am sharing both those formulas with you. You are requested to mix these two and make a formula so that I can extract two or three or four words from a line in a cell. Please Please (To extract one word from mid of line) =Trim(MID(SUBSTITUTE(B6," ",REPT(" ",LEN(B6))),(C6-1)*LEN(B6)+1,LEN(B6))) (To extract 2 or 3 word from the starting of the line) =TRIM(LEFT(B1,FIND("~",SUBSTITUTE(B1," ","~",A1)&"~")))
Karmesh, please ask your question in our forum and include your formulas there and, if it helps, include a sample file. Comments here are difficult to use to solve problems - I am replying only to this comment and not your others, although it was a good technique to get me to notice you, though not as good as asking in the forum ;) www.teachexcel.com/talk/microsoft-office?src=yt_comment
Great simple UDF to get the job done.. excellent! Here is my version.. tinkered with it a bit.. just for the practice. It gives the user some in-Cell feedback if wrong delimiter or word position out of range:
Function Extract_Text(Search As String, Delim As String, Position As Integer)
Dim arr() As String
arr = Split(Search, Delim)
If Position < 1 Or Position > UBound(arr) + 1 Or InStr(Search, Delim) = 0 Then
Extract_Text = "ERROR! - Syntax is: Extract_Text(Search, ""Delim"", Position)"
Else
Extract_Text = arr(Position - 1)
End If
End Function
Thanks for the inspiration to create. Thumbs up!
You're very welcome and thanks for watching! In your udf, I Love the idea of outputting the correct syntax along with the error in a udf!!!! I never thought of that and it really is a great way to make sure a user is aware of the syntax. Also, it's great to see different ways to do the same thing.)
Thank you, you are extremely helpful and clear understanding. Look forward to using you in the future.
You made my job easier. Thank you!!!
Thanks, this has eased my work. Its an amazing work out.
sweeeeeeet...... love you..!! exactly what i need
Thank you! the code helps me a lot
very useful sir thanks
Thanks , very useful
I want to extract characters between given characters or symbols range
Hi good day TeachExcel...this video is amazing for me...thanks for your kind sharing ^^
How do you extract a string from another string, such as 'bbb' from unstructured strings 12bbb4ty and 1w3bbbrt5? We need to match that somehow and then take it out and this is the regex kind of function. Is something like that an option in Excel? Either way, thanks for help 🙏!
@Briar Mario Yea, been watching on Flixzone} for months myself :D
@Briar Mario Definitely, have been using flixzone} for years myself :D
Hi i wanted to hget many words in a column from a one self define command??
Thank you
You're welcome Emmy)
how can i extract keywords and create a frequency distribution table in descending order, if I have thousands of data and lets say 4-5 different keywords
how to extract letter form a word or number?
for example the word is "Phenomenon " & I need to extract forth letter from this word that is "N"
is this possible in excel??????????????
I'm needing to create customer ID using first 3 letters of name and 3 letters of last name as a customer id.. example John Murphy = johmur
How can I do that?
Is there a variation of this that will allow me to pull all email addresses out of a spreadsheet that has them in various cells and columns?
Not really - you need a macro for this that will search the entire spreadsheet, identify an email address and then copy/paste it somewhere. This is a bit of a process. You can ask for help doing this on our forum or paid help via the Contact section if you like - www.teachexcel.com
I m guessing this will work for dates as well
Probably not - it depends how your dates are in excel. But you can use DAY() MONTH() YEAR() functions to get the parts of a date.
Is it possible to save these UDF on every excel workbook?
yes
what if you have approx 277 vendors and you need to extract just the vendor names from the journal line description
This is amazing, thank you. Anyway to stop #Value! When there is no word. So get word on 4th occurance but there is no word so it returns #Value!. Some sentences are 5 words long, some only 3.
the problem is, what if in the second row the word awesome is now the 5th word instead of 4th.
Hi. I have to extract 15 different words from 5 thousands cells "with different sentences" "in different order". Each cell contains one or two of these 15 words. Therefore your formula doesn't work as each cells are very different. Is there ar formula that can extract the mach word(s)? For example, if the 15 words are, apple, orange, lemon, etc, a formula like =Get_word(cell, "apple", "orange",,,,) and if matches one word, just leave it like "apple".
Is there a way to get more than one word? I have 1500 pus products and not every 7th word is what I need
This! I have the same problem.
I couldnt find the formula get word in excel.. Why?
That's v g8 🙏
Thanks :)
Sir, I request you to please make a formula in which I can extract two or three words from the middle of the line of a cell in Excel. I have made different formulas for both, but I am not able to form a formula by mixing them both. I am sharing both those formulas with you. You are requested to mix these two and make a formula so that I can extract two or three or four words from a line in a cell. Please Please
(To extract one word from mid of line)
=Trim(MID(SUBSTITUTE(B6," ",REPT(" ",LEN(B6))),(C6-1)*LEN(B6)+1,LEN(B6)))
(To extract 2 or 3 word from the starting of the line)
=TRIM(LEFT(B1,FIND("~",SUBSTITUTE(B1," ","~",A1)&"~")))
Karmesh, please ask your question in our forum and include your formulas there and, if it helps, include a sample file. Comments here are difficult to use to solve problems - I am replying only to this comment and not your others, although it was a good technique to get me to notice you, though not as good as asking in the forum ;)
www.teachexcel.com/talk/microsoft-office?src=yt_comment