Excel Experts,

Is there code that will edit a formula based on the text in the formula.

The formulas in my spreadsheet are similar to the following

A B C
1 =200-100
2 =3000-2400
3 =50-25

What I want to do is edit these formulas by stripping out the minus sign and
anything after the minus sign.

So I want to edit A1 "=200-100" by deleting "-100", so that the resulting
formula is "=200".

Is there code that would do this?

Thanks,
Alan

--
achidsey

Here is a function that does what you want (I think...)

Public Function BeforeMinus(ByVal Cell As Range) As String
Dim strReturnValue As String

strReturnValue = "Error"
If Left(Cell.Formula, 1) = "=" And InStr(Cell.Formula, "-") Then
strReturnValue = Left(Cell.Formula, InStr(Cell.Formula, "-") - 1)
End If
BeforeMinus = strReturnValue
End Function
--
HTH...

Jim Thomlinson


"achidsey" wrote:

Excel Experts,

Is there code that will edit a formula based on the text in the formula.

The formulas in my spreadsheet are similar to the following

A B C
1 =200-100
2 =3000-2400
3 =50-25

What I want to do is edit these formulas by stripping out the minus sign and
anything after the minus sign.

So I want to edit A1 "=200-100" by deleting "-100", so that the resulting
formula is "=200".

Is there code that would do this?

Thanks,
Alan

--
achidsey

Thanks Jim,

I want to put it in a sub procedure but hopefully I can make the coversion.

Alan
--
achidsey


"Jim Thomlinson" wrote:

Here is a function that does what you want (I think...)

Public Function BeforeMinus(ByVal Cell As Range) As String
Dim strReturnValue As String

strReturnValue = "Error"
If Left(Cell.Formula, 1) = "=" And InStr(Cell.Formula, "-") Then
strReturnValue = Left(Cell.Formula, InStr(Cell.Formula, "-") - 1)
End If
BeforeMinus = strReturnValue
End Function
--
HTH...

Jim Thomlinson


"achidsey" wrote:

Excel Experts,

Is there code that will edit a formula based on the text in the formula.

The formulas in my spreadsheet are similar to the following

A B C
1 =200-100
2 =3000-2400
3 =50-25

What I want to do is edit these formulas by stripping out the minus sign and
anything after the minus sign.

So I want to edit A1 "=200-100" by deleting "-100", so that the resulting
formula is "=200".

Is there code that would do this?

Thanks,
Alan

--
achidsey

Hi achidsey

Try...

Sub Test()
Range("A1:A100").Replace What:="-*", Replacement:="", LookAt:=xlPart
End Sub

--


XL2003
Regards

William



"achidsey" (notmorespam) wrote in message
...
Excel Experts,

Is there code that will edit a formula based on the text in the formula.

The formulas in my spreadsheet are similar to the following

A B C
1 =200-100
2 =3000-2400
3 =50-25

What I want to do is edit these formulas by stripping out the minus sign
and
anything after the minus sign.

So I want to edit A1 "=200-100" by deleting "-100", so that the resulting
formula is "=200".

Is there code that would do this?

Thanks,
Alan

--
achidsey