extracting data from string

[siddharthsopori]

Hi Guys ,


I am in a crunch situation.


I want to extract data from a set of strings.


Eg.

Product A123 does not exist in plant 111

Product B123 does not exist in plant 222

Product C123 does not exist in plant 333


so i should be able to extract

Products. A123 ,B123 & C123 in one sheet

and plants 111 ,222 n 333 in another sheet.


Regards

Siddharth

 

[Luke M]

Here's a generic format you could use to extract strings.

=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH($B$1,A1)+LEN($B$1)+1,999)," ",REPT(" ",999)),999))


Where A1 is the cell containing sentence, and B1 contains the word preceding the word you want to extract. In your case, that would be either "product" or "plant".

 

[Faseeh]

Besides what Luke said you could also use:

[pre]

=TRIM(MID(A1,FIND("Product",A1,1)+8,4)) &
=TRIM(MID(A1,FIND("plant",A1,1)+5,4))

[/pre]

Your strings are in A1:A3


Regards,

 

[siddharthsopori]

Thanks a lot for your prompt help Luke and Faseeh

 

[Luke M]

Always glad to help, thanks for the feedback. =)

 

[Trader273]

if you want to go the vba route, the string functions (InStr, StrComp, StrConv) are perfect for what you want. Just depends on if you want to go that way or not.