VBA Lookup Help

[quickc2]

Hi, any help would be greatly appreciated.

I have worksheet with multiple columns of information in it with my M column containing a lot of information at once.

The information in column M always begins with "Door Surf Color:" followed by a 4-6 letter word.

I want a function that will lookup the word that follows "Door Surf Color:" and pastes it into the same row in column AT.



Thank you!

 

[Joe4]

Your question isn't quite clear.

Are you simply looking to extract the word that follows "Door Surf Color:", or are you trying to match the word up to something?
If the later, what are you trying to match it up to, and what are you wanting to return?

An actual example may help clarify what you are after.
There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[quickc2]

Hi, thanks for the reply.

For example, if my cell M2 begins "Door Surf Color:GK_26 Window blind..." I want to be able to copy the 'GK_26' and paste it into the same row in the AT column (AT2). The small deviance is in the amount of letters is in the code so the macro must be able to do codes that are different lengths.

 

[Joe4]

Do you really need VBA, or can you just use a formula like this:

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(M2,"Door Surf Color:","")," ",REPT(" ",100)),100))

 

[Joe4]

We could create a simple User Defined Function in VBA to do this, like this:

Function GetValue(myString As String) As String

    Dim temp As String
    Dim arr1() As String
    Dim arr2() As String
    
    arr1 = Split(myString, ":")
    arr2 = Split(arr1(1), " ")
    
    GetValue = arr2(0)

End Function

Then you could use this User Defined Function either on the sheet directly, or in VBA, i.e.

=GetValue(M2)

(on the sheet)

 

[quickc2]

That is perfect! Thank you. I was overthinking it with a VBA but had not used these functions before. You learn something new everyday.

 

[Joe4]

You are welcome!