Find a value in a string specific to a text

[just_lookin_009]

Column Data	Result
NW-Nrth(20% / $2.0M),	0
SW-Rail(0% / $0.M),	0%
NW-Calif(5% / 390M), SW-DMA(5% / $39.0M), UGD-UGD W(2 / 179.M),	5%
,	0
East-Trans(20% / $80.0M), SW-Rail(20% / $8.0M),	20%
TISW-TIC G(40% / $10.0M,SW-Rail(20% / $5.0M)	20%

<tbody>
</tbody>

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]

Column Data	Result
NW-Nrth(20% / $2.0M),	0
SW-Rail(0% / $0.M),	0%
NW-Calif(5% / 390M), SW-DMA(5% / $39.0M), UGD-UGD W(2 / 179.M),	5%
,	0
East-Trans(20% / $80.0M), SW-Rail(20% / $8.0M),	20%
TISW-TIC G(40% / $10.0M,SW-Rail(20% / $5.0M)	20%

<tbody>
</tbody>

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.