Extract Data from Last "" marks

[Dojorgen]

My current formula is not consistent enough. There is variation so my 118 digits varies. The consistency is that it is always the last "" marks, is there a formula that only displays the text within the last "" marks.

Below is the common text string and the data need would be the last "" marks.


Text:
'CMD/A="ALARM_REPORT"MID/A="vfei2W1234"MTY/A="E" ECD/U4=0 ETX/A="" ALARM_ID/14=860828980 ALARM_STATE/U1=1 ALARM_TEXT/A="IN/OUT-SLIDER PORT1 C SENS2 ERROR""

Current Formula:
=IF(F2="Alarm","",IF(F2="Alarm",MID(D2,118[/B],200),""))

 

[dreid1011]

Perhaps this:

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"""","@",LEN(A1)-LEN(SUBSTITUTE(A1,"""",""))-1),"@",REPT(" ",99)),99)),"""","")

 

[Dojorgen]

Wow that was nice what is the 99 represent? is that the number of digits?

It presents: IN/OUT-SLIDER PORT1 C SENS2 ERROR'

Is there a way to get ride of the ' tick mark at the end of the text?

 

[dreid1011]

The 99 is pulling the right most 99 characters of the string after I inserted 99 extra spaces. You might change that to a larger number if the desired string can be more than 98 characters long.

As far as the single quote on the end, is it before or after the last double quote " ?

 

[Dojorgen]

Yes sorry i gave it to you wrong
"IN/OUT-SLIDER PORT1 C SENS2 ERROR""

should be

"IN/OUT-SLIDER PORT1 C SENS2 ERROR"'

looks like there is a single " then after that '. Not double quotes

 

[dreid1011]

Ok, try this:

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"""","@",LEN(A1)-LEN(SUBSTITUTE(A1,"""",""))-1),"@",REPT(" ",99)),99)),"""'","")

 

[Dojorgen]

impressive, thank you very much

 

[dreid1011]

You're welcome.

 

[jtakw]

Hi,

This would also work, C1 formula with quote marks, C2 formula without:

Excel 2010
	A	B	C
1	'CMD/A="ALARM_REPORT"MID/A="vfei2W1234"MTY/A="E" ECD/U4=0 ETX/A="" ALARM_ID/14=860828980 ALARM_STATE/U1=1 ALARM_TEXT/A="IN/OUT-SLIDER PORT1 C SENS2 ERROR""		"IN/OUT-SLIDER PORT1 C SENS2 ERROR"
2			IN/OUT-SLIDER PORT1 C SENS2 ERROR
Sheet1

Cell Formulas
Range	Formula
C1	=CHAR(34)&TRIM(RIGHT(SUBSTITUTE(A1,CHAR(34),REPT(" ",100)),300))&CHAR(34)
C2	=TRIM(RIGHT(SUBSTITUTE(A1,CHAR(34),REPT(" ",100)),300))