I would like to avoid using two cells to represent the following:
2017 TOTAL PPs. (all in one cell)
I would like to use only the number part of the cell contents in a formula.
Can it be done?

=1*2017 & " total pps"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"paulg" wrote in message
...
I would like to avoid using two cells to represent the following:
2017 TOTAL PPs. (all in one cell)
I would like to use only the number part of the cell contents in a
formula.
Can it be done?

Perhaps a Custom Number Format?

With
A1: 2017

Try something like this:
Select A1
From the Excel main menu:
<edit<format<number tab
Category: Custom
Type: General" TOTAL PPs"
Click [OK]

That way the A1 will will display:
2017 TOTAL PPs
....but it will contain the number 2017

Any cells referencing A1 will be able to use the numeric contents.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"paulg" wrote:

I would like to avoid using two cells to represent the following:
2017 TOTAL PPs. (all in one cell)
I would like to use only the number part of the cell contents in a formula.
Can it be done?

Thanks I can live with it and it works just fine.

"Ron Coderre" wrote:

Perhaps a Custom Number Format?

With
A1: 2017

Try something like this:
Select A1
From the Excel main menu:
<edit<format<number tab
Category: Custom
Type: General" TOTAL PPs"
Click [OK]

That way the A1 will will display:
2017 TOTAL PPs
...but it will contain the number 2017

Any cells referencing A1 will be able to use the numeric contents.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"paulg" wrote:

I would like to avoid using two cells to represent the following:
2017 TOTAL PPs. (all in one cell)
I would like to use only the number part of the cell contents in a formula.
Can it be done?

Not sure how to apply this.
Outcome is to use number from this cell and number from another cell in a
formula of a third cell. When I applied this suggestion to two cells and
then created a formula in third cell I still had error.

However, Ron Coderre's reply suggestion helped satisfy my needs.

THANKS.

"Don Guillett" wrote:

=1*2017 & " total pps"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"paulg" wrote in message
...
I would like to avoid using two cells to represent the following:
2017 TOTAL PPs. (all in one cell)
I would like to use only the number part of the cell contents in a
formula.
Can it be done?

This function call will pull out the leading number from a cell...

LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

It returns a #N/A error if there is no number in the cell, so you may want
to wrap it in an IF function call if that is a possibility.

Rick


"paulg" wrote in message
...
I would like to avoid using two cells to represent the following:
2017 TOTAL PPs. (all in one cell)
I would like to use only the number part of the cell contents in a
formula.
Can it be done?

Thanks everyone for your responses

"Rick Rothstein (MVP - VB)" wrote:

This function call will pull out the leading number from a cell...

LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

It returns a #N/A error if there is no number in the cell, so you may want
to wrap it in an IF function call if that is a possibility.

Rick


"paulg" wrote in message
...
I would like to avoid using two cells to represent the following:
2017 TOTAL PPs. (all in one cell)
I would like to use only the number part of the cell contents in a
formula.
Can it be done?