Examine text string and abbreviate it

[Burrgogi]

Hello, I have the following text in column D:

96% with 3335 reviews (Overwhelmingly Positive)
95% with 971 reviews (Overwhelmingly Positive)
84% with 6341 reviews (Very Positive)
88% with 4622 reviews (Very Positive)
73% with 157 reviews (Mostly Positive)
80% with 64 reviews (Positive)

Is there a macro that can examine the words in parenthesis and abbreviated in a separate column? for example: Take "Very Positive" from the text string and have it displayed as "VP" in a separate column.
(Just taking the first letter of each word). The only exception would be when the review is just "(Positive)" only -- see the very last line in the sample I provided above. In such a case, I would like to have it abbreviated as "Pos". If that latter part is too difficult, I'm happy to just settle for just the first letter of each word; OP, VP, MP.

I would like column C to contain the final result. Like so:
OP
OP
VP
VP
MP
Pos

 

[Joe4]

Try this (for a value in cell D1):

=MID(D1,FIND("(",D1)+1,1) & IFERROR(MID(D1,FIND("(",D1)+FIND(" ",MID(D1,FIND("(",D1)+1,LEN(D1)))+1,1),"")

If "Pos" is the only one that will ever come from one word, you could use:

=MID(D1,FIND("(",D1)+1,1) & IFERROR(MID(D1,FIND("(",D1)+FIND(" ",MID(D1,FIND("(",D1)+1,LEN(D1)))+1,1),"os")

to get "Pos" instead of just "P".

 

[Burrgogi]

Try this (for a value in cell D1):

=MID(D1,FIND("(",D1)+1,1) & IFERROR(MID(D1,FIND("(",D1)+FIND(" ",MID(D1,FIND("(",D1)+1,LEN(D1)))+1,1),"")

If "Pos" is the only one that will ever come from one word, you could use:

=MID(D1,FIND("(",D1)+1,1) & IFERROR(MID(D1,FIND("(",D1)+FIND(" ",MID(D1,FIND("(",D1)+1,LEN(D1)))+1,1),"os")

to get "Pos" instead of just "P".

OK so your first formula works just fine, I was able to do a simple manual copy & paste into cell C2. Alternatively, I was also able to incorporate your formula into my current macro. It was written by a board member about a year ago that does several things to the worksheet in question.

I can incorporate the first formula into my macro - no problem. But incorporating both of your formulas into my macro poses a challenge for me.

 

[Joe4]

I can incorporate the first formula into my macro - no problem. But incorporating both of your formulas into my macro poses a challenge for me.

You only need one formula or the other, not both. They both work on ALL records, with slightly different results in one case. Choose the one you want.
Note that that theu both work on ALL records. The only difference is that the second will return "Pos" for "(Positive)" entries, while the first will return "P".

So it really depends on whether you want to see "P" or "Pos".
And note the "Pos" solution only works in "Positive" is the only single-word option between parentheses that you have.

 

[Burrgogi]

You only need one formula or the other, not both. They both work on ALL records, with slightly different results in one case. Choose the one you want.
Note that that theu both work on ALL records. The only difference is that the second will return "Pos" for "(Positive)" entries, while the first will return "P".

So it really depends on whether you want to see "P" or "Pos".
And note the "Pos" solution only works in "Positive" is the only single-word option between parentheses that you have.

OK I understand now.

It looks like I spoke too soon. I thought I could incorporate your formula into my VBA routine myself. I tried the following and I keep getting an error. I'm sure it's a syntax error of some sort, but I can't figure out how to correct it.

    With Range("C2:C" & lastRow)
        .Formula = "=MID(D2,FIND(""("",D2)+1,1) & IFERROR(MID(D2,FIND(""("",D2)+FIND(" ",MID(D2,FIND(""("",D2)+1,LEN(D2)))+1,1),"os")
    End With

Error message: Expected end of statement

All i'm trying to do is fill column C (starting from the 2nd row) with your new formula and have it fill down for as many rows that exists with text values.

 

[Burrgogi]

OK, nevermind - I played around with it and found the solution. I incorporated this into my routine and now it works perfectly:

    Range("C2").Select
    Range("C2:C" & Cells(Rows.Count, "D").End(xlUp).Row).Formula = "=MID(D2,FIND(""("",D2)+1,1) & IFERROR(MID(D2,FIND(""("",D2)+FIND("" "",MID(D2,FIND(""("",D2)+1,LEN(D2)))+1,1),""os"")

"

 

[Joe4]

Things can get a little messy with all the double-quotes, as VBA uses those for Text Qualifiers.
Here is a little trick. If you want to get a formula you have on your worksheet into VBA, you can let Excel figure it out for you.
Simply turn on your Macro Recorder and record yourself entering the formula into any cell on your workbook.
Then turn off your Macro Recorder, and look at the code you just recorded. It will have the exact syntax you need to assign that formula in VBA!

 

[Burrgogi]

Rats.....I just encountered

Things can get a little messy with all the double-quotes, as VBA uses those for Text Qualifiers.
Here is a little trick. If you want to get a formula you have on your worksheet into VBA, you can let Excel figure it out for you.
Simply turn on your Macro Recorder and record yourself entering the formula into any cell on your workbook.
Then turn off your Macro Recorder, and look at the code you just recorded. It will have the exact syntax you need to assign that formula in VBA!

OK cool - I didn't think of doing that! Thanks for the tip.

I do have one last minute question. I have encountered yet another category. "Mixed" reviews.
65% with 157 reviews (Mixed)

How can I modify your formula to accommodate for this 'Mixed' category?

Sorry I didn't mention this sooner, I've been busy all day running errands so this is my first chance to play with things. Plus, I barely had anything to eat all day so I'm a little light headed.

 

[Joe4]

Rats.....I just encountered


OK cool - I didn't think of doing that! Thanks for the tip.

I do have one last minute question. I have encountered yet another category. "Mixed" reviews.
65% with 157 reviews (Mixed)

How can I modify your formula to accommodate for this 'Mixed' category?

Sorry I didn't mention this sooner, I've been busy all day running errands so this is my first chance to play with things. Plus, I barely had anything to eat all day so I'm a little light headed.

That gets a bit tricky. If you have Excel 365, you might be able to use a LET function. But if you using 2010, you will not have that.

You could probably use a LOOKUP or VLOOKUP formula.

If those are the only 5 options you have, then the LOOKUP formula would look like this:

=LOOKUP(MID(D1,FIND("(",D1)+1,1) & IFERROR(MID(D1,FIND("(",D1)+FIND(" ",MID(D1,FIND("(",D1)+1,LEN(D1)))+1,1),""),{"M","MP","OP","P","VP"},{"Mix","MP","OP","Pos","VP"})

If you want to go the VLOOKUP route, you would set up a 2 column lookup table, the first column returning the abbreviations my first formula returned, and the second being what you want to display. So if we set up that lookup table in the range I2:J6, like this:

Then this is the formula we would need to put in cell E1 and copy down:

=VLOOKUP(MID(D1,FIND("(",D1)+1,1) & IFERROR(MID(D1,FIND("(",D1)+FIND(" ",MID(D1,FIND("(",D1)+1,LEN(D1)))+1,1),""),$I$2:$J$6,2,0)

 

[Burrgogi]

Awesome, thanks Joe4 - I've decided to use your LOOKUP formula and I now have it successfully incorporated into my VBA routine.

Much thanks for your help today. It's the little things that put a smile on my face these days.