Can I have formula that uses a cell with numeric value and text string?

[onelane]

I have a spread sheet that i use for job costing and estimating. I work in the concrete construction industry so i deal a lot with cubit yards and square and linear feet.

I have a cell that calculates the cubic yardage of a 4" thick slab at "x" square feet which is in another cell. We buy the concrete material by the yard but we charge by the square footage.

I want to be able to have a cell that says say "1500 Sq Ft" and a cell that says "$4.00". From this i want to be able to use just the 1500 times the $4.00 to equal $6000 in another cell. but i get error messages because of the text string "Sq Ft" in the first cell.

Is there a way to use this without separating into 2 cells, perhaps a way to take just the numeric part of the cell to be used in the formula?

Thanks
Aric

 

[barry houdini]

Hello Aric

Instead of having 1500 Sq Ft as the cell value you could use a custom format in that cell

Format > Cells > number > custom and type this in the box

0" Sq Ft"

Now if you type in 1500 the cell will display as 1500 Sq Ft but the cell value will be numeric......so your calculations will work

 

[Andrew Poulsom]

You can format the cell as eg

0" Sq Ft"

and just enter the value.

 

[doofusboy]

OK, let's say you have "1500 sq. ft." in cell A1.
And in cell B1 you have "4" formatted as currency so it displays as "$4.00"

In cell C1 you could put the following formula and format the cell as currency so that it displays "$6,000.00"

=LEFT(A1,LEN(A1)-8)*B1

The -8 is to get rid of " sq. ft."

 

[onelane]

Thank you all. I will try a couple of those options and see what works best. Greatly appreciate the help.

 

[onelane]

Hello Aric

Instead of having 1500 Sq Ft as the cell value you could use a custom format in that cell

Format > Cells > number > custom and type this in the box

0" Sq Ft"

Now if you type in 1500 the cell will display as 1500 Sq Ft but the cell value will be numeric......so your calculations will work

Thanks Barry, that worked perfect.

Thanks to everyone else, Ill save the other ways to do it also...

 

[onelane]

is there a way that i can use a decimal value that displays as architectural measurement, for instance if the value entered was 8.83 would be displayed as 8'10"?

This would be a huge help as i calculate cubic volumes in yardage based off concrete foundation wall heights such as 8'10" tall by 8, 10 or 12" thick wall. right now I have to have seperate cells to do so but would greatly simplify the spreadsheet to have it in one cell.


thanks
aric

 

[pgc01]

Hi Aric

There's a solution that might be useful to you,where you hide part of the format.

In the custom format box, enter:

0' 0\"/12

This would format the value 8.83 as

8' 10"/12

now in the custom format box click between the " and the / and press CTRL-J. Then click ok for the format.

This causes the value to be displayed as:

8' 10"

which is what you want, as long as the row has the usual 1 line height.

The truth is that when you press the CTRL-J, you are in fact inserting a line feed, so what is displayed is

8' 10"
/12

You can see it if you make the row height bigger, but as long as you keep the row height with the default 1 line height the display will be ok, and you can use the cell in calculations as the value is still the 8.83

Does this help?

 

[onelane]

thank you, that is what i need. but for some reason when i do it i get a box with a question mark in it where the ctrl j is inserted and the /12 still shows up? but the values are correct in calculation? maybe its cause im on a mac running xp on vmware fusion? ill try tomorrow on my xp computer at work, maybe will display different?

 

[pgc01]

thank you, that is what i need. but for some reason when i do it i get a box with a question mark in it where the ctrl j is inserted and the /12 still shows up? but the values are correct in calculation? maybe its cause im on a mac running xp on vmware fusion? ill try tomorrow on my xp computer at work, maybe will display different?

I can't test in the MAC, but
- I forgot to tell you that you have to set the Format->Alignment->Wrap Text
- instead of CTRL-J you can also press ALT and then with ALT down press in the keypad 010. This is another way to insert the line feed (at least in the pc).