Extracting a character string from the Midde

[CatsIgnoreMe]

Hi,
I've reviewed previous forum posts and attempted to figure out how to do this. No luck. I'm looking for a formula(s) that can extract the a. choice, the b. choice, the c. choice, and the d. choice (see example below).

It does not need to be an all encompassing formula (if that's easier for you) containing all 4 choices. I'm happy with a formula that I can use with extracting the a. choice, and then modifying it to extract the b. choice, etc.. I'm using Excel 2016.

[TABLE="width: 503"]
<tbody>[TR]
  [TD="class: xl63, width: 503"][INDENT]Which value rolls up to the  Master in forecasting? Choose 1.  a.  Expected revenue  b. Quota Amount  c. Opportunity amount  d. Product Quantity[/INDENT]
[/TD]
[/TR]
</tbody>[/TABLE]
[code]

Thank you for your help. It's much appreciated!

 

[SteveO59L]

something along the lines of

=MID(F8,FIND("a.",F8),FIND("b.",F8)-FIND("a.",F8))

 

[Scott T]

Try you will need to change the a. to the choice you are extracting and b. to the next choice.

=TRIM(MID(A2,SEARCH("a.",A2),SEARCH("b.",A2)-SEARCH("a.",A2)))

for the last choice just put a big number in so that it pulls the whole choice

=TRIM(MID(A2,SEARCH("d.",A2),9999999))

 

[Joe4]

Assuming your entry is in A1, here are formulas you can use:
Choice a: =MID(A1,FIND(" a. ",A1)+4,FIND(" b. ",A1)-FIND(" a. ",A1)-4)
Choice b: =MID(A1,FIND(" b. ",A1)+4,FIND(" c. ",A1)-FIND(" b. ",A1)-4)
Choice c: =MID(A1,FIND(" c. ",A1)+4,FIND(" d. ",A1)-FIND(" c. ",A1)-4)
Choice d: =MID(A1,F IND(" d. ",A1)+4,LEN(A1))

 

[AlanY]

similarly

Excel 2012
	A	B	C	D	E
1		a.	b.	c.	d.
2	Which value rolls up to the Master in forecasting? Choose 1. a. Expected revenue b. Quota Amount c. Opportunity amount d. Product Quantity	Expected revenue	Quota Amount	Opportunity amount	Product Quantity
Sheet4

Cell Formulas
Range	Formula
B2	=TRIM(IF(C1="",TRIM(RIGHT($A2,LEN($A2)-FIND(B$1,$A2)-1)),MID($A2,FIND(B$1,$A2)+2,FIND(C$1,$A2)-FIND(B$1,$A2)-2)))

 

[CatsIgnoreMe]

Try you will need to change the a. to the choice you are extracting and b. to the next choice.

=TRIM(MID(A2,SEARCH("a.",A2),SEARCH("b.",A2)-SEARCH("a.",A2)))

for the last choice just put a big number in so that it pulls the whole choice

=TRIM(MID(A2,SEARCH("d.",A2),9999999))

Fantastic! Worked beautifully. I have new appreciation for the TRIM function.

 

[CatsIgnoreMe]

Assuming your entry is in A1, here are formulas you can use:
Choice a: =MID(A1,FIND(" a. ",A1)+4,FIND(" b. ",A1)-FIND(" a. ",A1)-4)
Choice b: =MID(A1,FIND(" b. ",A1)+4,FIND(" c. ",A1)-FIND(" b. ",A1)-4)
Choice c: =MID(A1,FIND(" c. ",A1)+4,FIND(" d. ",A1)-FIND(" c. ",A1)-4)
Choice d: =MID(A1,F IND(" d. ",A1)+4,LEN(A1))

This worked well also. I did want to keep the "a." as part of the character string so I modified your formula removing the +4 and -4 and replacing with +1 and -1

Choice a: =MID(A1,FIND(" a. ",A1)+1,FIND(" b. ",A1)-FIND(" a. ",A1)-1)

Thank you for responding so quickly. I look forward to carefully parsing the formulas offered to improve my Excel skills.

 

[CatsIgnoreMe]

similarly

Excel 2012

	A	B	C	D	E
a.	b.	c.	d.
Which value rolls up to the Master in forecasting? Choose 1. a. Expected revenue b. Quota Amount c. Opportunity amount d. Product Quantity

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA"]Expected revenue[/TD]
[TD="bgcolor: #E2EFDA"]Quota Amount[/TD]
[TD="bgcolor: #E2EFDA"]Opportunity amount[/TD]
[TD="bgcolor: #E2EFDA"]Product Quantity[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=TRIM(IF(C1="",TRIM(RIGHT($A2,LEN($A2)-FIND(B$1,$A2)-1)),MID($A2,FIND(B$1,$A2)+2,FIND(C$1,$A2)-FIND(B$1,$A2)-2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Interesting approach I'll remember for future reference. I'm sure I'll need this at some point!

 

[Joe4]

This worked well also. I did want to keep the "a." as part of the character string so I modified your formula removing the +4 and -4 and replacing with +1 and -1

I wasn't quite clear on that, but am glad to see you were able to figure out how the formula works and were able to adjust it.

BTW, like the user name. Don't feel too bad. Cats ignore pretty much everybody!

 

[CatsIgnoreMe]

I wasn't quite clear on that, but am glad to see you were able to figure out how the formula works and were able to adjust it.

BTW, like the user name. Don't feel too bad. Cats ignore pretty much everybody!

After spending 3 hours trying to figure this out, I appreciate the quick and multiple useful responses. Thank you to everyone. And yeah, somehow I stumbled into a short and memorable user name. Generally I'm not that creative.