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

You dont need a formula. Try the below

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

If this post helps click Yes
---------------
Jacob Skaria


"Jdude" wrote:

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

A formula would be as below..

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

If this post helps click Yes
---------------
Jacob Skaria


"Jdude" wrote:

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

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

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

--
Rick (MVP - Excel)


"Jdude" wrote in message
...
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

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!


"Rick Rothstein" wrote:

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

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

--
Rick (MVP - Excel)


"Jdude" wrote in message
...
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