number and text in cell for formula

[paulg]

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?

 

[Don Guillett]

=1*2017 & " total pps"

 

[Ron Coderre]

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]

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

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

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?

 

[paulg]

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.

 

[Rick Rothstein \(MVP - VB\)]

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]

Thanks everyone for your responses

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