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?

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

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

Luke M
*Remember to click "yes" if this post helped you!*


"diana" wrote:

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?

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

"Luke M" wrote:

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

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

Luke M
*Remember to click "yes" if this post helped you!*


"diana" wrote:

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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"diana" wrote in message
...
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?

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"diana" wrote:

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

"Luke M" wrote:

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

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

Luke M
*Remember to click "yes" if this post helped you!*


"diana" wrote:

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?

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

"Luke M" wrote:

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

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

Luke M
*Remember to click "yes" if this post helped you!*


"diana" wrote:

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?

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
A formula approach
=LEFT(J5,FIND(" ",J5)-1)+1& " years"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"diana" wrote in message
...
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?

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"

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
A formula approach
=LEFT(J5,FIND(" ",J5)-1)+1& " years"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"diana" wrote in message
...
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?

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.



diana wrote:

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?

--

Dave Peterson