string

[deepakqlikview_123]

Below is string

S&P 500 Consumer Discretionary Sector Index = Discretionary EPS % change

Industrials EPS % change

S&P 500 Consumer Staples Sector Index = Staples EPS % change

Energy EPS % change

out of above i need to extract

S&P 500 Consumer Discretionary Sector Index = Discretionary

Industrials

S&P 500 Consumer Staples Sector Index = Staples

Energy

Respectively, can you please suggest which string function shall i use.

thanks

[Qrishna · Accepted solution]

If the positions of the substrings does not change in the field, then you can use

Field A

S&P 500 Consumer Discretionary Sector Index

S&P 500 Consumer Staples Sector Index

Field B

Discretionary EPS%  change  Industrials EPS % change

Staples         EPS%  change  Energy EPS % change

solution:

Subfield(Field B,' ',1) & ' ' & Subfield(Field B,' ',4)

let me know if it works for you after checking.

thanks

[Clever_Anjos]

Maybe

Subfield(Field B,'=',1) & ' ' & Subfield(Subfield(Field B,'=',2),' ',1) & ' ' & Subfield(Subfield(Field B,'=',2),' ',5)

should return what you need

[Qrishna]

ya, if

S&P 500 Consumer Discretionary Sector Index = Discretionary EPS % change Industrials EPS % change

S&P 500 Consumer Staples Sector Index = Staples EPS % change Energy EPS % change

are data values of one single field then you can use cleveranjos‌ expression:

Subfield(Field B,'=',1) & ' ' & Subfield(Subfield(Field B,'=',2),' ',1) & ' ' & Subfield(Subfield(Field B,'=',2),' ',5)

else

if they belong to different fields

Field A

S&P 500 Consumer Discretionary Sector Index

S&P 500 Consumer Staples Sector Index

Field B

Discretionary EPS%  change  Industrials EPS % change

Staples         EPS%  change  Energy EPS % change

then us expressions

solution1:

Subfield(Field B,' ',1) & ' ' & Subfield(Field B,' ',4)

let me know which one works for you after checking.

[rwunderlich]

=TextBetween(text, '','EPS')

-Rob

http://masterssummit.com

http://robwunderlich.com

[MarcoWedel]

=Replace(yourstring,'EPS % change','')

[MarcoWedel]

hope this helps

regards

Marco