Question with IF Function

Closed
mikelangelo - Jan 3, 2015 at 11:31 AM
vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
- Jan 4, 2015 at 08:36 AM
Hello,

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:

"Allwinner
Cube
PIPO"

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?

Thanks,
Mike

1 reply

vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
250
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).

=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.
0