Combine Substitute & Right

[Bravo2003]

I'm going round in loops trying to combine my formula
Cell text contains various make up of a text string
"Market Cap: LargeInvestment Style: Growth" (please note, there is no space between "Large" & "Investment" in all cells and the space between Style: and next word is not consistent.)

Need to get the text to become Large-Growth
these 2 parts of the string vary in each cell - Large, Mid, Small, etc.. / Growth, Value, Blend, etc...

I have made 2 formula to get part way
=SUBSTITUTE(P2,"Investment Style: ","-")
gets me to
"Market Cap: Mid-Growth"

and separately I have
=RIGHT(P2,LEN(P2)-FIND(": ",P2))
This removes the "Market Cap: " section to give
" MidInvestment Style: Growth"

So i don't have to add columns to do this in stages, was trying to get the 2 formula in to 1 column to combine the process?
Am I on the right track or is there a better option?

 

[Sufiyan97]

I'm going round in loops trying to combine my formula
Cell text contains various make up of a text string
"Market Cap: LargeInvestment Style: Growth" (please note, there is no space between "Large" & "Investment" in all cells and the space between Style: and next word is not consistent.)

Need to get the text to become Large-Growth
these 2 parts of the string vary in each cell - Large, Mid, Small, etc.. / Growth, Value, Blend, etc...

I have made 2 formula to get part way
=SUBSTITUTE(P2,"Investment Style: ","-")
gets me to
"Market Cap: Mid-Growth"

and separately I have
=RIGHT(P2,LEN(P2)-FIND(": ",P2))
This removes the "Market Cap: " section to give
" MidInvestment Style: Growth"

So i don't have to add columns to do this in stages, was trying to get the 2 formula in to 1 column to combine the process?
Am I on the right track or is there a better option?

Try

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(P2,"Investment Style: ","-"),":",REPT(" ",99)),99))

 

[Bravo2003]

Try

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(P2,"Investment Style: ","-"),":",REPT(" ",99)),99))

Thanks, yes that works, just an issue where the space between the last word is not consistent, so it gives either
Mid-Growth
or
Mid- Growth

Any way to remove the unwanted space?

 

[Sufiyan97]

Thanks, yes that works, just an issue where the space between the last word is not consistent, so it gives either
Mid-Growth
or
Mid- Growth

Any way to remove the unwanted space?

Can you post some more examples of your data using below link?

 

[Sufiyan97]

Otherwise Try

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C8,"Investment Style: ","-"),":",REPT(" ",99)),99))," ","")

 

[Bravo2003]

Can you post some more examples of your data using below link?

Data is as this:

Market Cap: Mid Investment Style: Growth

Market Cap: Large Investment Style: Growth

Market Cap: Large Investment Style: Blend

Market Cap: Mid Investment Style: Growth

Market Cap: Large Investment Style: Growth

Market Cap: Large Investment Style: Growth

Market Cap: Mid Investment Style: Growth

Market Cap: Large Investment Style: Growth

Market Cap: Large Investment Style: Blend

Market Cap: Mid Investment Style: Value

Market Cap: Mid Investment Style: Blend

Market Cap: Large Investment Style: Value

Market Cap: Large Investment Style: Blend

Market Cap: Mid Investment Style: Blend

Market Cap: Large Investment Style: Blend

Market Cap: Large Investment Style: Value

Market Cap: Small Investment Style: Blend

Market Cap: Large Investment Style: Blend

 

[Bravo2003]

Otherwise Try

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C8,"Investment Style: ","-"),":",REPT(" ",99)),99))," ","")

That's cleared it up. Thanks

 

[Sufiyan97]

That's cleared it up. Thanks

You are welcome!
Glad to help

 

[jtakw]

Hi,

Just another way:

Book3.xlsx
	A	B
1	Market Cap: LargeInvestment Style: Growth	Large-Growth
Sheet924

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(":",A1)+1,99),"Investment Style: ","-")," ","")