Question with IF Function

mikelangelo - Jan 3, 2015 at 11:31 AM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Jan 4, 2015 at 08:36 AM

I was wondering, if I have for example in C2, C3, C4 the next info:

"Tableta Onda V989 Allwinner A80 Octa Core 9.7 Inch Sistem Operare Android 4.4
Tableta Cube U65GT TALK9X 3G MTK8392 Octa Core 9.7 Inch Sistem Operare Android 4.4.2
Tableta PIPO P9 RK3288 Quad Core 1.8GHz 10.1 Inch Sistem Operare Android 4.4 "

how to use a formula to show in D2, D3, D4 only certain words from the text above, like so:


I want to extract from the title the names of the tablets, for example and if I use the IF function, i only have true or false values, instead of values like "if certain text is Allwinner, use Allwinner", "if certain text is Cube, use Cube", "if certain word is PIPO, use PIPO".

Can you please help me with this?


1 response

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
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):-

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).


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:-


and to extract the last word use:-


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.


N.B. : A special thanks to our friends at Ozgrid for their assistance on this one.