Question with IF Function
Closed
mikelangelo

Jan 3, 2015 at 11:31 AM
vcoolio
vcoolio
 Posts
 1356
 Registration date
 Thursday July 24, 2014
 Status
 Moderator
 Last seen
 August 11, 2022
Related:
 Question with IF Function
 Formula Question: "IF" function with date ✓  Forum  Excel
 MS Excel 2007  Quick Function Question ✓  Forum  Excel
 Functionality of wifi  Guide
 What are accessor and mutator functions in c++  Guide
 Which function can be used to directly bring automated data from one worksheet to another worksheet  Guide
1 reply
vcoolio
Jan 4, 2015 at 08:36 AM
 Posts
 1356
 Registration date
 Thursday July 24, 2014
 Status
 Moderator
 Last seen
 August 11, 2022
Jan 4, 2015 at 08:36 AM
Hello Mikelangelo,
You could use this formula in each of the cells D2, D3, D4:
=MID(MID(MID(SUBSTITUTE(C3," ","^",3),1,256),FIND("^",SUBSTITUTE(C3," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(C3," ","^",3),1,256),FIND("^",SUBSTITUTE(C3," ","^",3)),256))2)
This formula will find the fourth word in each row, such as "Allwinner".
However, as the words that you want to extract are in different positions in each row, then you would have to change the word reference number (in the above formula, the reference is 3) in each formula in each row. So, if you were looking to extract the word "Cube" in the second row, you would need to change the reference to 1.
As this formula can be somewhat overwhelming, it may be best to use a UDF (user defined function) incorporating the above formula as follows (we'll call it the Find_Word function):
Place the code in a standard module in your VB Editor.
Now, all you need do is to go back to your worksheet and place the following formula in the cells D2, D3, D4 (or however many cells you need to place it).
=Find_Word(C2,4)
This will extract the fourth word in C2 "Allwinner".
BUT, for the other two rows, you will need to change the 4 into 2 to extract "Cube" or "Pipo". This would be a less cumbersome way to extract words than just using the formula alone.
So, to make the whole task simpler, it may be worth rearranging your input so that the words that you want to extract are in the same position on each row (say position 2). I would suggest position 1 ( the first word) as, incorporated in the code, are the "First" and "Last" functions which will extract the first or last words for you.
If you want to extract the first word, then use the following formula in cells D2:D4:
=Find_Word(C2,"First")
and to extract the last word use:
=Find_Word(C2,"Last")
I would suggest using the first position, then you will only need to use the "First" function formula in each cell.
I hope this helps you.
Regards,
vcoolio.
N.B. : A special thanks to our friends at Ozgrid for their assistance on this one.
You could use this formula in each of the cells D2, D3, D4:
=MID(MID(MID(SUBSTITUTE(C3," ","^",3),1,256),FIND("^",SUBSTITUTE(C3," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(C3," ","^",3),1,256),FIND("^",SUBSTITUTE(C3," ","^",3)),256))2)
This formula will find the fourth word in each row, such as "Allwinner".
However, as the words that you want to extract are in different positions in each row, then you would have to change the word reference number (in the above formula, the reference is 3) in each formula in each row. So, if you were looking to extract the word "Cube" in the second row, you would need to change the reference to 1.
As this formula can be somewhat overwhelming, it may be best to use a UDF (user defined function) incorporating the above formula as follows (we'll call it the Find_Word function):
Option Compare Text Function Find_Word(text_string As String, nth_word) As String Dim lWordCount As Long With Application.WorksheetFunction lWordCount = Len(text_string)  Len(.Substitute(text_string, " ", "")) + 1 If IsNumeric(nth_word) Then nth_word = nth_word  1 Find_Word = Mid(Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _ .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256), 2, _ .Find(" ", Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _ .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256))  2) ElseIf nth_word = "First" Then Find_Word = Left(text_string, .Find(" ", text_string)  1) ElseIf nth_word = "Last" Then Find_Word = Mid(.Substitute(text_string, " ", "^", Len(text_string)  _ Len(.Substitute(text_string, " ", ""))), .Find("^", .Substitute(text_string, " ", "^", _ Len(text_string)  Len(.Substitute(text_string, " ", "")))) + 1, 256) End If End With End Function
Place the code in a standard module in your VB Editor.
Now, all you need do is to go back to your worksheet and place the following formula in the cells D2, D3, D4 (or however many cells you need to place it).
=Find_Word(C2,4)
This will extract the fourth word in C2 "Allwinner".
BUT, for the other two rows, you will need to change the 4 into 2 to extract "Cube" or "Pipo". This would be a less cumbersome way to extract words than just using the formula alone.
So, to make the whole task simpler, it may be worth rearranging your input so that the words that you want to extract are in the same position on each row (say position 2). I would suggest position 1 ( the first word) as, incorporated in the code, are the "First" and "Last" functions which will extract the first or last words for you.
If you want to extract the first word, then use the following formula in cells D2:D4:
=Find_Word(C2,"First")
and to extract the last word use:
=Find_Word(C2,"Last")
I would suggest using the first position, then you will only need to use the "First" function formula in each cell.
I hope this helps you.
Regards,
vcoolio.
N.B. : A special thanks to our friends at Ozgrid for their assistance on this one.