Extracting a piece of info from text

[Smiley1977]

Hello Fellow Excel Users! I need help with the folowing: I get a report with system performance results, and each step has a bunch of text like this :" st1_humlike/Status=0, st2_login/Status=0, st3_HumVit/Status=0, st4_ViewMyDashboard/Status=1, st5_FitnessExercise/Status=n/a, st6_PartnerHealthClubs/Status=n/a, st7_FindHealthClub/Duration (ms)=n/a, st7_FindHealthClub/Status=n/a" and I need to extract a piece of information that is equal to 1. In this example it will be this piece "st4_ViewMyDashboard/Status=1". Could anyone make a suggestion on how I can do that? I'm not aware of any formulas that would look for something that = 1, and go back to last comma to grab the entire expression from a comma to a comma. Any suggestions would be greatly appreciated.

 

[Jonmo1]

Welcome to the board...

Try

=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND("=1",A1)+2,LEN(A1),"")," ",REPT(" ",LEN(A1))),LEN(A1)))


Hope that helps.

 

[T. Valko]

Hello Fellow Excel Users! I need help with the folowing: I get a report with system performance results, and each step has a bunch of text like this :" st1_humlike/Status=0, st2_login/Status=0, st3_HumVit/Status=0, st4_ViewMyDashboard/Status=1, st5_FitnessExercise/Status=n/a, st6_PartnerHealthClubs/Status=n/a, st7_FindHealthClub/Duration (ms)=n/a, st7_FindHealthClub/Status=n/a" and I need to extract a piece of information that is equal to 1. In this example it will be this piece "st4_ViewMyDashboard/Status=1". Could anyone make a suggestion on how I can do that? I'm not aware of any formulas that would look for something that = 1, and go back to last comma to grab the entire expression from a comma to a comma. Any suggestions would be greatly appreciated.

Try this...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("=1",A1)+1)," ",REPT(" ",100)),100))

 

[Smiley1977]

Welcome to the board...

Try

=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND("=1",A1)+2,LEN(A1),"")," ",REPT(" ",LEN(A1))),LEN(A1)))


Hope that helps.

_____
Thank you very much! I tried and I'm getting #VALUE! error, and I can't figure out why.
<TABLE style="WIDTH: 337pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=449 border=0><COLGROUP><COL style="WIDTH: 263pt; mso-width-source: userset; mso-width-alt: 12800" width=350><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 110.25pt" height=147><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: black 0.5pt solid; WIDTH: 263pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 110.25pt; BACKGROUND-COLOR: #ee7777" width=350 height=147>st1_huma/Status=0, st2_login/Status=0, st3_HumVitality/Status=0, st4_ViewMyDashboard/Status=0, st5_GetRewarded_Mall/Status=1, st6_GotoCinema/Status=n/a, st7_TheaterLocation/Duration (ms)=n/a, st7_TheaterLocation/Status=n/a</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle width=99>#VALUE!</TD></TR></TBODY></TABLE>

 

[Smiley1977]

Try this...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("=1",A1)+1)," ",REPT(" ",100)),100))

__________________
Thank you for trying to help me with this. I'm getting an error, not sure why
<TABLE style="WIDTH: 337pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=449 border=0><COLGROUP><COL style="WIDTH: 263pt; mso-width-source: userset; mso-width-alt: 12800" width=350><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 110.25pt" height=147><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: black 0.5pt solid; WIDTH: 263pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 110.25pt; BACKGROUND-COLOR: transparent" width=350 height=147>st1_humStatus=0, st2_login/Status=1, st3_HumVitality/Status=n/a, st4_ViewMyDashboard/Status=n/a, st5_FitnessExercise/Status=n/a, st6_PartnerHealthClubs/Status=n/a, st7_FindHealthClub/Duration (ms)=n/a, st7_FindHealthClub/Status=n/a</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle width=99>#VALUE!

</TD></TR></TBODY></TABLE>

 

[Jonmo1]

Both formulas work for me...
Is that ALL in ONE cell?

Make sure you're changing the A1's to reflect the actual cell address containing that string.

 

[T. Valko]

__________________
Thank you for trying to help me with this. I'm getting an error, not sure why
<TABLE style="WIDTH: 337pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=449 border=0><COLGROUP><COL style="WIDTH: 263pt; mso-width-source: userset; mso-width-alt: 12800" width=350><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 110.25pt" height=147><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: black 0.5pt solid; WIDTH: 263pt; PADDING-TOP: 0.75pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 110.25pt; BACKGROUND-COLOR: transparent" width=350 height=147>st1_humStatus=0, st2_login/Status=1, st3_HumVitality/Status=n/a, st4_ViewMyDashboard/Status=n/a, st5_FitnessExercise/Status=n/a, st6_PartnerHealthClubs/Status=n/a, st7_FindHealthClub/Duration (ms)=n/a, st7_FindHealthClub/Status=n/a</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle width=99>#VALUE!


</TD></TR></TBODY></TABLE>

Works OK for me.

Here's a small file that demonstrates this.

zzzSmiley1977.xls 15kb

http://cjoint.com/?AGCcffplzH6