CODEWITHSUNDEEP
excelvbaexcel-formula
kkshinichi
kkshinichi

Reputation: 61

Extract Specific Text from a String using functions

I had these strings that has no particular patterns:

- ControlF Job Name: WEB_GG_WPP_PRD_C_C_ELASTICOWL Status: Ended Not Ok  Odate: 04/20/2019 Run Count: 2

- ControlF: WEB_WP_UPP_PRD_C_C_ELASTICOWL  Ended Not Ok  Odate: 4/21  RunCount: 2

-  ControlF: WEB_PP_BPP_PRD_C_D_ELASTICWEEKLY  Status: Ended not OK   Odate: 4/29   Run count: 1

- CONTROLF:   Job Name: WEB_PP_EDM_PRD_T_C_HP1_CTGGDALTA Status: Ended NOT OK Odate: 6/26/2017 RunCount: 2

- CONTROLF:   Job Name: WEB_EZ_APP_PRD_C_D_3RDPANTYUPAC Status: Ended NOT OK Odate: 6/16/2016 RunCount: 1

I need to extract these WEB_* string (e.g. WEB_GG_WPP_PRD_C_C_ELASTICOWL) from these using Excel. I tried using Text-to-Columns, but the string I need is placed all over the sheet. So my questions will be:

  1. If using Text-to-Columns, how can I pull only the WEB_* string?
  2. How will it be if I'll just use formulas?
  3. What will be the code for this using VBA?

Thanks!

EDIT: Attached screenshot for more examples

Upvotes: 0

Views: 61

Answers (1)

Mikku
Mikku

Reputation: 6654

This should do the Job for You:

Function : =MID(D6,FIND("WEB",D6,1),FIND("Status",D6,1)-FIND("WEB",D6,1))

  • Updated Function to use

  • MID(D6,FIND("WEB",D6,1),FIND(" ",D6,FIND("WEB",D6,1))-FIND("WEB",D6,1))

enter image description here

You can run same function on Range in VBA to get this result in a Loop.

Upvotes: 2

Related Questions