If statement for alpha numeric combo AND numeric only

[Jdude]

I need a formula that will strip out an alpha from a number:
B1=12,562.25CR
B2=25,235.44

I want one formula in column C that will do two things.
1) if there is a CR, take it off and multiply the remaining value by (-1)
=LEFT(B1,SEARCH("cr",B1)-1)*(-1)
2) if there isn't a CR, just give me the value in that cell

the expected results for the above would be:
C1= -12,562.25
C2= 25,235.44

 

[Jacob Skaria]

You dont need a formula. Try the below

Select the column.
Hit Ctrl+H to find and replace CR with -
From menu Data>Text to Columns>Next>Next> hit Advanced button
Make sure 'Trailing minus sign' is checked.
Hit finish

If this post helps click Yes

 

[Jacob Skaria]

A formula would be as below..

=IF(ISERROR(FIND("CR",B1)),B1,SUBSTITUTE(B1,"CR",)*-1)

If this post helps click Yes

 

[Rick Rothstein]

Here is a formula that will do what you want...

=IF(RIGHT(B1,2)="cr",-LEFT(B1,LEN(B1)-2),B1)

 

[Jdude]

OMG...this is awesome! I love ya man! Thank you so much. I wanted a
formula so I didn't have to write a crazy macro....like replace CR, then go
to column and do text2columns....

Thank you...this will save so much time!