Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Return formula minus = sign
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Return formula minus = sign
Is there any way to get the code below to return formula in cell B4 without
the = sign. So if the cell contains =10+10+4.50 I want only 10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Return formula minus = sign

Patrick Simonds wrote:
Is there any way to get the code below to return formula in cell B4 without
the = sign. So if the cell contains =10+10+4.50 I want only 10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text

try using this:

Cells(rowindex,columnindex).Replace What:="=", Replacement:=""

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Return formula minus = sign
textbox93.text = mid(worksheets("Income").range("b4").formula,2)



Patrick Simonds wrote:

Is there any way to get the code below to return formula in cell B4 without
the = sign. So if the cell contains =10+10+4.50 I want only 10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text

--

Dave Peterson
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 110
Default Return formula minus = sign
Hi Patrick

Try below please.


Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(Range("D8"))
End Sub


'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+/-'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Or _
Mid(cell.Formula, 2, 1) = "-" Then

OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function



--
Best Regards
Joergen Bondesen


"Patrick Simonds" wrote in message
...
Is there any way to get the code below to return formula in cell B4
without the = sign. So if the cell contains =10+10+4.50 I want only
10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Return formula minus = sign
I can understand stripping the + sign from after the initial = sign, but I don't
think removing =- would be a good idea. That negative sign looks important to
the formula.

=-a1+b1
would return
a1+b1
And that doesn't look like it reflects what the formula really does.

Joergen Bondesen wrote:

Hi Patrick

Try below please.

Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(Range("D8"))
End Sub

'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+/-'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Or _
Mid(cell.Formula, 2, 1) = "-" Then

OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
Is there any way to get the code below to return formula in cell B4
without the = sign. So if the cell contains =10+10+4.50 I want only
10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text


--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 110
Default Return formula minus = sign
Hi Dave.

Thanks. You are quite right.
My brain must have been disabeled for a few seconds.
Notice the way I am using mid with 2 parameters. 8-)
Hope you can approve belove.


Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(ActiveCell)
Stop
End Sub


'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903a
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Then
OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function


--
Best regards
Joergen Bondesen


"Dave Peterson" wrote in message
...
I can understand stripping the + sign from after the initial = sign, but I
don't
think removing =- would be a good idea. That negative sign looks
important to
the formula.

=-a1+b1
would return
a1+b1
And that doesn't look like it reflects what the formula really does.

Joergen Bondesen wrote:

Hi Patrick

Try below please.

Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(Range("D8"))
End Sub

'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+/-'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Or _
Mid(cell.Formula, 2, 1) = "-" Then

OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
Is there any way to get the code below to return formula in cell B4
without the = sign. So if the cell contains =10+10+4.50 I want
only
10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text


--

Dave Peterson


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Return formula minus = sign
I'm not sure I'd check anything but .hasformula (drop the typename() and
isempty() checks???)

This is what I use to return the formula (including the equal sign) (just for
comparison purposes):

Option Explicit
Function GetFormula(Rng As Range)

Dim myFormula As String
GetFormula = ""
With Rng.Cells(1)
If .HasFormula Then
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
Else
myFormula = .FormulaR1C1
End If
If .HasArray Then
GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GetFormula = myFormula
End If
End If
End With
End Function

Joergen Bondesen wrote:

Hi Dave.

Thanks. You are quite right.
My brain must have been disabeled for a few seconds.
Notice the way I am using mid with 2 parameters. 8-)
Hope you can approve belove.

Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(ActiveCell)
Stop
End Sub

'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903a
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Then
OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
I can understand stripping the + sign from after the initial = sign, but I
don't
think removing =- would be a good idea. That negative sign looks
important to
the formula.

=-a1+b1
would return
a1+b1
And that doesn't look like it reflects what the formula really does.

Joergen Bondesen wrote:

Hi Patrick

Try below please.

Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(Range("D8"))
End Sub

'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+/-'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Or _
Mid(cell.Formula, 2, 1) = "-" Then

OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
Is there any way to get the code below to return formula in cell B4
without the = sign. So if the cell contains =10+10+4.50 I want
only
10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text


--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 110
Default Return formula minus = sign
Hi Dave.

I am very pleased for your advise, and I do hope, I do you credit with
below.
Thanks.


Sub test()
Dim MyString As String
MyString = OnlyString(ActiveCell)
Stop
End Sub


'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060904
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
' Function select always cell(1).
'----------------------------------------------------------
'
Function OnlyString(Rng As Range) As String
OnlyString = ""

With Rng.Cells(1)
If .HasFormula Then
If Mid(.Formula, 2, 1) = "+" Then
OnlyString = _
Mid(.Formula, 3, Len(.Formula) - 2)
Else
OnlyString = _
Mid(.Formula, 2, Len(.Formula) - 1)
End If
End If
End With
End Function


--
Best regards
Joergen Bondesen


"Dave Peterson" wrote in message
...
I'm not sure I'd check anything but .hasformula (drop the typename() and
isempty() checks???)

This is what I use to return the formula (including the equal sign) (just
for
comparison purposes):

Option Explicit
Function GetFormula(Rng As Range)

Dim myFormula As String
GetFormula = ""
With Rng.Cells(1)
If .HasFormula Then
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
Else
myFormula = .FormulaR1C1
End If
If .HasArray Then
GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GetFormula = myFormula
End If
End If
End With
End Function

Joergen Bondesen wrote:

Hi Dave.

Thanks. You are quite right.
My brain must have been disabeled for a few seconds.
Notice the way I am using mid with 2 parameters. 8-)
Hope you can approve belove.

Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(ActiveCell)
Stop
End Sub

'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903a
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Then
OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
I can understand stripping the + sign from after the initial = sign, but
I
don't
think removing =- would be a good idea. That negative sign looks
important to
the formula.

=-a1+b1
would return
a1+b1
And that doesn't look like it reflects what the formula really does.

Joergen Bondesen wrote:

Hi Patrick

Try below please.

Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(Range("D8"))
End Sub

'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+/-'
' Note : Eg. =5+2 = '5+2' or =+B5+C3 = 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Or _
Mid(cell.Formula, 2, 1) = "-" Then

OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
Is there any way to get the code below to return formula in cell B4
without the = sign. So if the cell contains =10+10+4.50 I want
only
10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text


--

Dave Peterson

--

Dave Peterson


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to handle a minus sign "-" as text not as part of a formula? Claudia d'Amato Excel Discussion (Misc queries) 3 April 2nd 23 07:09 PM
Minus Sign JLMcCabes Excel Discussion (Misc queries) 1 March 30th 11 01:34 PM
I do not want the minus sign Steved Excel Worksheet Functions 5 December 8th 08 09:26 PM
Dash or Minus sign shos as zero in formula Darla New Users to Excel 2 March 8th 07 03:09 PM
minus sign tom mcdonald Excel Worksheet Functions 1 April 19th 06 09:58 AM


All times are GMT +1. The time now is 06:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement