Question with IF Function
Closed
mikelangelo
-
Jan 3, 2015 at 11:31 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jan 4, 2015 at 08:36 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jan 4, 2015 at 08:36 AM
Related:
- Question with IF Function
- Agp function - Guide
- Yahoo mail forgot password and security question - Guide
- Network card function - Guide
- Find function on mac - Guide
- Function of insert key - Guide
1 response
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Jan 4, 2015 at 08:36 AM
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 re-arranging 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 re-arranging 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.
