Extracting a word from a cell in Excel

[qureshi]

Hi,
If I have a cell (Excel)which contains a value like Group 200: 4019 Compatible. How can i extract the 4019 portion.
The sheet that I have has values like this:

Group 201: 4019 Compatible 'Want to extract 4019
Group 202: 4019 Premium 'Want to extract 4019
Group 203: AX Compatible 'Want to extract AX
Group 204: AX EXP'Want to extract AX

Is there some way in excel vba to select a word like the Ctrl Key and some other combination and then maybe create a macro.

Thanks

 

[SBendBuckeye]

Build a formula in stages as below assuming that the word you want to extract is always the first word after the initial colon ":".

=Find(":", A1) => 10
=Trim(Mid(A1, Find(":", A1) + 1, 100)) => "4019 Compatable"

The above then becomes the base of another Find/Mid combination based on " " instead of ":"

=FIND(" ", TRIM(MID(A1,FIND(":",A1)+1,100))) => 5
=TRIM(LEFT(TRIM(MID(A1,FIND(":",A1)+1,100)),FIND(" ", TRIM(MID(A1,FIND(":",A1)+1,100))))) => "4019"

Sorry this gets real ugly to do in one formula. The string
"TRIM(MID(A1,FIND(":",A1)+1,100))" which returns everything to the right of the colon ":" then becomes the basis of the next Find with a space as well as the argument for the Left function. If A = "TRIM(MID(A1,FIND(":",A1)+1,100))" then
the last part becomes =TRIM(LEFT(A,FIND(" ",A))) => "4019".

If you break it down like this you can watch the progression to help see how if fits together:

A1 = "Group 201: 4019 Compatible"
B1 = =Find(":", A1) => 10
C1 = =Mid(A1, B1 + 1, 100) => " 4019 Compatible"
D1 = =Trim(C1) => "4019 Compatable"
E1 = =Find(" ", D1) => 5
F1 = =Left(D1, E1) => "4019 "
G1 = =Trim(F1) = "4019"

Hope this helps and good luck!

 

[SBendBuckeye]

If your data is fixed as in your example so that the word you want to extract ALWAYS begins at character 11 then things get a lot easier as below:

=Mid(A1, 12, 100) => "4019 Compatible"
=Find(" ", Mid(A1, 12, 100)) => 5
=TRIM(LEFT(MID(A1,12,100),FIND(" ",MID(A1,12,100))))

As above in pieces:

B1 = =Mid(A1, 12, 100) => "4019 Compatible"
C1 = =Find(" ", B1) => 5
D1 = =Trim(Left(B1, C1)) => "4019"

Good Luck!

 

[qureshi]

SBendBuckEye:
Thanks for the input. To be honest the first tip went just off my head and I thought of dropping this project. Yes my data is always the same as you described so the secong options should be fine and "its a lots easy to understand"
I will give it a try.

Thanks