How do I add 1 to a number in column b

[diana]

I have a spreadsheet in which column b refers to the number of years of safe
driving. ie "22 years". How do I add 1 to each cell in column B?

 

[Luke M]

If you literally wrote "22 years" into a single cell, the manipulation needed
is:

=LEFT(B2,FIND(" ",B2)-1)+1&" years"

 

[diana]

I didn't use quotation marks. it's just 22 years

 

[Don Guillett]

A formula approach
=LEFT(J5,FIND(" ",J5)-1)+1& " years"

 

[Luke M]

Sorry, I should have made that clearer. The real question was to confirm that
you were combining the value (22) with text (years) into the same cell.
(which you are)

The formula I gave you will take the value from the cell, add 1 to it, then
add on the text string. Simply place it into an empy column and copy down as
needed.

 

[diana]

No, I didn't use quotation marks and I need to change column b for each row
in my spreadsheet.

 

[Don Guillett]

A macro solution
Sub addnumbertotext()
'=LEFT(J5,FIND(" ",J5)-1)+1& " years"
For Each c In Range("b2:b22") 'Selection
c.Value = Left(c, InStr(c, " ") - 1) + 1 & " years"

Next c
End Sub

 

[Rick Rothstein]

Assuming the cells in the range contain either whole numbers or, if floating
point numbers are used, the decimal point for the system is a "dot", then
this statement...

c.Value = Left(c, InStr(c, " ") - 1) + 1 & " years"

can be replaced with this slightly simpler one...

c.Value = Val(c.Value) + 1 & " years"

 

[Dave Peterson]

Instead of putting the string " years" in each cell, couldn't you just add it to
the column header--then everyone will know that field is.

If you like that idea, you could remove the " years" from each cell by:
Selecting the range
Edit|replace (xl2003 menus)
what: _years (where _ means space character)
with: (leave blank)
replace all

==========
If you don't like that idea, you could still remove the string from each cell,
but then give the column a custom format:

Format|cells|number tab|custom category
#0" years"

Then the value will still be numeric (for further calculations), but the cell
will look pretty.