# Convert a + sign to a number and add that number

##### Board Regular
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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``

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``````

Hi Norie,
The second calculation will happen everytime, even if there is not a + present.

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).

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.

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.

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.

I'm still getting that same dialog box. It can't find the macro. I pasted it into the VBA editor for that worksheet.

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...

Replies
14
Views
1K
Replies
3
Views
192
Replies
1
Views
157
Replies
3
Views
218
Replies
8
Views
375

1,219,799
Messages
6,150,320
Members
450,951
Latest member
kh198

### 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.

### Which adblocker are you using?

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

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