number and text in cell for formula
 

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?

number and text in cell for formula
 

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

number and text in cell for formula
 

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?

number and text in cell for formula
 

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?

number and text in cell for formula
 

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?

number and text in cell for formula
 

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?

number and text in cell for formula
 

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?