Help with MID Formula

[soniasyc]

Hi all,

I have some questions that can either be set out with a : or a ?
If the question is
Was this reported: Yes
I use the formula = MID(A7,FIND(":",A7)+2,(LEN(A7))) so that the cell adjacent can extract just the word Yes

If the question is
Were there any animals? There was a dog and a cat
I use the formula = MID(A29,FIND("?",A29)+2,(LEN(A29))) so that the cell adjacent can extract just the response There was a dog and a cat

I'm having trouble combining the two formulas so that regardless of if it is a : or a ? it will still extract the answer/response into the adjacent cell.
Can someone please help me with making this work?

Thanks heaps in advance

[AlKey]

Assuming string is in A1
Enter formula in B1 and copy down

Please Login or Register  to view this content.

v	A	B
1	Was this reported: Yes	Yes
2	Were there any animals? There was a dog and a cat	There was a dog and a cat

[FlameRetired]

Hi soniasyc and welcome to the forum.

If you upload an Excel workbook file with sample data (not pictures or screen shots please) it will be easier and you usually get more and faster response.

Please be sure to desensitize the data.


To do so follow these steps.

• To attach a file to your post,
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• The file name will appear at the bottom of your reply.

[soniasyc]

Thank you AlKey
That seemed to work however, if there is a time in the answer eg. 12:30pm it would only provide 30pm in the adjacent cell, is there a way around this?
I've attached a sample workbook as per FlameRetired's suggestions.
Thanks

[AlKey]

Then this

Please Login or Register  to view this content.

v	A	B
1	Was this reported? Yes	Yes
2	Time of report: 8 : 30 AM	8 : 30 AM
3	Action taken: Letter generated	Letter generated
4	Has a letter been sent? No	No
5	Has a response been received? No	No

[Phuocam]

Or try this ...

=MID(A1,FIND(":",SUBSTITUTE(A1,"?",":"))+2,LEN(A1))

[soniasyc]

Thank you so much AlKey, that works perfectly.
Really appreciate your help with that