Find a value in a string specific to a text

[just_lookin_009]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column Data[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]NW-Nrth(20% / $2.0M),[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]SW-Rail(0% / $0.M),[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]NW-Calif(5% / 390M), SW-DMA(5% / $39.0M), UGD-UGD W(2 / 179.M), [/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD],[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]East-Trans(20% / $80.0M), SW-Rail(20% / $8.0M),[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]TISW-TIC G(40% / $10.0M,SW-Rail(20% / $5.0M)[/TD]
[TD]20%[/TD]
[/TR]
</tbody>[/TABLE]

I have a column that changes values but I need to find the % value after the "KSW-Rail" or "KSW-DMA" text, there are around 800 lines of data that will change/ update weekly

Thanks for your help!

 

[skywriter]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column Data[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]NW-Nrth(20% / $2.0M),[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]SW-Rail(0% / $0.M),[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]NW-Calif(5% / 390M), SW-DMA(5% / $39.0M), UGD-UGD W(2 / 179.M),[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD],[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]East-Trans(20% / $80.0M), SW-Rail(20% / $8.0M),[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]TISW-TIC G(40% / $10.0M,SW-Rail(20% / $5.0M)[/TD]
[TD]20%[/TD]
[/TR]
</tbody>[/TABLE]

I have a column that changes values but I need to find the % value after the "KSW-Rail" or "KSW-DMA" text, there are around 800 lines of data that will change/ update weekly

Thanks for your help!

Looking at your sample data, it appears the text that you really want to search by is not KSW-Rail or KSW-DMA, but SW-Rail and SW-DMA, is that correct?

 

[just_lookin_009]

Looking at your sample data, it appears the text that you really want to search by is not KSW-Rail or KSW-DMA, but SW-Rail and SW-DMA, is that correct?

Yes thank you for the correction

 

[István Hirsch]

If there is no W-Rail in the text try this:

=TRIM(MID(A2,MIN(FIND({"W-Rail","SW-DMA"},A2&"W-RailSW-DMA"))+7,3))

 

[just_lookin_009]

If there is no W-Rail in the text try this:

=TRIM(MID(A2,MIN(FIND({"W-Rail","SW-DMA"},A2&"W-RailSW-DMA"))+7,3))

Thank you worked like a charm! I did not know about the trim or mid functions, was trying to use the left and right.

 

[István Hirsch]

You are welcome, thanks for the feedback.