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.)
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.
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 🙏!
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?
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??????????????
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".
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
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
