Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate in a Cell (&) and working with the numbers.
I have this function on E1,
=TEXT(B1-A1),"$ #,##0.00")&" - "&TEXT(D1/C1),"0.00%") I cannot work with the numbers that E1 provides because they are formatted as text. Is there a way to format the numbers in two different ways ($,%), just like my function, but to actually be able to work with these?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate in a Cell (&) and working with the numbers.
You may be able to parse your string into pieces, but I'd just use two cells to
hold the formulas and then format those cells nicely--not using the =text() function. If you absolutely have to use that formula that results in a string, then how about adding a couple more cells with just the plain old formulas in them. You could even insert a couple of rows/columns and hide them so they're out of the way. GEM wrote: I have this function on E1, =TEXT(B1-A1),"$ #,##0.00")&" - "&TEXT(D1/C1),"0.00%") I cannot work with the numbers that E1 provides because they are formatted as text. Is there a way to format the numbers in two different ways ($,%), just like my function, but to actually be able to work with these?? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate in a Cell (&) and working with the numbers.
Ok, thank you very much!! But is there a way to use them inside the same cell
and leave them as a working number not as text?? "Dave Peterson" wrote: You may be able to parse your string into pieces, but I'd just use two cells to hold the formulas and then format those cells nicely--not using the =text() function. If you absolutely have to use that formula that results in a string, then how about adding a couple more cells with just the plain old formulas in them. You could even insert a couple of rows/columns and hide them so they're out of the way. GEM wrote: I have this function on E1, =TEXT(B1-A1),"$ #,##0.00")&" - "&TEXT(D1/C1),"0.00%") I cannot work with the numbers that E1 provides because they are formatted as text. Is there a way to format the numbers in two different ways ($,%), just like my function, but to actually be able to work with these?? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate in a Cell (&) and working with the numbers.
One cell can only hold one number. You can make into text as you have done
and then represent the 2 numbers but now if you want the numbers back you either have to recalculate the values or parse out the numbers (parsing out the numbers will be a lot more work than it is worth). So the short answer to your question is no... -- HTH... Jim Thomlinson "GEM" wrote: Ok, thank you very much!! But is there a way to use them inside the same cell and leave them as a working number not as text?? "Dave Peterson" wrote: You may be able to parse your string into pieces, but I'd just use two cells to hold the formulas and then format those cells nicely--not using the =text() function. If you absolutely have to use that formula that results in a string, then how about adding a couple more cells with just the plain old formulas in them. You could even insert a couple of rows/columns and hide them so they're out of the way. GEM wrote: I have this function on E1, =TEXT(B1-A1),"$ #,##0.00")&" - "&TEXT(D1/C1),"0.00%") I cannot work with the numbers that E1 provides because they are formatted as text. Is there a way to format the numbers in two different ways ($,%), just like my function, but to actually be able to work with these?? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate in a Cell (&) and working with the numbers.
You can use a complex formula to parse the values that you want. But you'll
have to use that complex formula in every cell that needs that number. It's not worth the trouble in my opinion. GEM wrote: Ok, thank you very much!! But is there a way to use them inside the same cell and leave them as a working number not as text?? "Dave Peterson" wrote: You may be able to parse your string into pieces, but I'd just use two cells to hold the formulas and then format those cells nicely--not using the =text() function. If you absolutely have to use that formula that results in a string, then how about adding a couple more cells with just the plain old formulas in them. You could even insert a couple of rows/columns and hide them so they're out of the way. GEM wrote: I have this function on E1, =TEXT(B1-A1),"$ #,##0.00")&" - "&TEXT(D1/C1),"0.00%") I cannot work with the numbers that E1 provides because they are formatted as text. Is there a way to format the numbers in two different ways ($,%), just like my function, but to actually be able to work with these?? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate Formula not working | Excel Discussion (Misc queries) | |||
Concatenate with Line Feed not working | Excel Discussion (Misc queries) | |||
Concatenate function not working for me | Excel Discussion (Misc queries) | |||
Working with range of numbers in a cell and searching same | Excel Discussion (Misc queries) | |||
Working with range of numbers in a cell and searching same | Excel Worksheet Functions |