Convert a + sign to a number and add that number

schmadly

Board Regular
Joined
Mar 29, 2006
Messages
79
Issue (2 parts): I type in numbers that have a space such as 22 56. Sometimes there may be a decimal such as 22 56.7 and sometimes there will be a + sign such as 22 56.7+ (the + sign represents .5 and I need the cell to recognize this so that when I type in 22 56.7, it converts it into 22 57.2 (+ added as a value of .5 and the + sign should now be gone from the cell).

Part 2: Assuming the + is converted I need the value I type in to be crunched according to a certain formula. If I type in 22 56.4 (or whatever the number is), I need it to perform: 56.4/32+22
In this part, the + sign should not convert to a .5 - it needs to divide 56.4 by 32 and then add 22 to that. Make sense?

What I have tried (but don't know enough to get them to work)
1. A Constant in VBA?
2. "For each" variable?

Any thoughts?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Maybe this?

Code:
=LEFT(A1,SEARCH(" ",A1)-1)+IF(RIGHT(MID(A1,SEARCH(" ",A1),255))="+",LEFT(MID(A1,SEARCH(" ",A1),255),LEN(MID(A1,SEARCH(" ",A1),255))-1)+0.5,MID(A1,SEARCH(" ",A1),255)+0)/32
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Will the second calculation only happen if the + was present?

Are you looking for code or formula?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrVal
    arrVal = Split(Target.Value, " ")
    
    If Right(arrVal(UBound(arrVal)), 1) = "+" Then
        arrVal(UBound(arrVal)) = Val(Left(arrVal(UBound(arrVal)), Len(arrVal(UBound(arrVal)) - 1))) + 0.5
    End If
    Application.EnableEvents = False
    Target.Value = Val(arrVal(UBound(arrVal))) / 32 + arrVal(LBound(arrVal))
    Application.EnableEvents = True
End Sub
 

schmadly

Board Regular
Joined
Mar 29, 2006
Messages
79
Hi Norie,
The second calculation will happen everytime, even if there is not a + present.
 

schmadly

Board Regular
Joined
Mar 29, 2006
Messages
79

ADVERTISEMENT

Oaktree, thanks - is this code you gave? If so, what do I add to it to put it into VBA (I just pasted it into a blank sub and it didn't like that).
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
schmadly

Oaktree's post is a formula not code.

The code I posted should do the calculation regardless if the + is present, I just asked because that wasn't clear from your post.
 

schmadly

Board Regular
Joined
Mar 29, 2006
Messages
79

ADVERTISEMENT

Hi Norie,
Code would be better if possible. I tried to run your code but couldn't get it to go - I think it may have something to do with the fact that it is a Private Sub, yes? It keeps pulling up the run a macro dialog box.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
The code I posted is intended to go in the worksheet module of whatever worksheet you want this to occur.

To access that right click the worksheet tab and select View Code.

Note the code isn't really tested - you didn't actually post any expected results.:)
 

schmadly

Board Regular
Joined
Mar 29, 2006
Messages
79
I'm still getting that same dialog box. It can't find the macro. I pasted it into the VBA editor for that worksheet.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Sorry I don't know what dialog box you are referring to.:)

The code I posted should run automatically when a value is entered on the worksheet, you do not need to goto Tools>Macros...
 

Forum statistics

Threads
1,136,519
Messages
5,676,333
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top