Formula Help Needed. TRIM(RIGHT(SUBSTITUTE with REPT and LEN count.

[lakejoftis]

I'm working with data in the B column that contains either a "+" or a "-" and I need the information after that.

Example values in B column:

> §3 +60% STAT
» (3 -15% STAT

My current formula(s) in the C column are either:

=TRIM(RIGHT(SUBSTITUTE(B1,"+",REPT(" ",LEN(B1))),LEN(B1)))
=TRIM(RIGHT(SUBSTITUTE(B1,"-",REPT(" ",LEN(B1))),LEN(B1)))

The formula in the C column will spit out:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>60% STAT
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>15% STAT

My problem is I need to combine those two formulas into one so it will recognize a "+" or a "-" in the data. I currently have to manually specify which formula to use in the C column based on if that value has a "+" or "-".

Can anyone help me combine those two formulas to recognize a "+" or a "-" ?

Thank you!


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[Fluff]

Hi & welcome to MrExcel
How about
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(B1,"+",REPT(" ",100)),"-",REPT(" ",100)),100))

 

[lakejoftis]

Wow thank you! That works perfectly!

Is there a way to adjust this formula to include the "+" or "-" in the output/substituted text?

 

[Fluff]

How about
=MID(B1,IFERROR(FIND("+",B1),FIND("-",B1)),LEN(B1))

 

[Rick Rothstein]

Here is a formula that you can try...

=REPLACE(A1,1,MIN(FIND("-",A1&"-"),FIND("+",A1&"+"))-1,"")

 

[lakejoftis]

@Fluff This works and leaves with me a #VALUE field for all invalid data type entries which is great for visual organizing and works well with the filter function!
@Rick Rothstein This works as well and leaves blank cells for invalid data type entries which will work well if I don't need to filter or want to bypass my filter function needs!

I wanted to explain the output as a reminder for myself and others who may search this thread in the future.

Thank you both so much for your help. All issues from my original post have been resolved.

 

[Fluff]

Glad we could help & thanks for the feedback