Convert a + sign to a number and add that number

[schmadly]

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?

 

[Oaktree]

Maybe this?

=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]

Will the second calculation only happen if the + was present?

Are you looking for code or formula?

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]

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

 

[schmadly]

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]

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]

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]

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]

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]

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