text and value in single cell
Posted by Michael Roper on May 22, 2001 11:06 AM
I need to include a value togther with text in a single cell, however I want the value only to be recognized in a formula. I believe there is a symbol to ensure that the text shows but is not a part of the value.
Thanks for any help
Posted by Loren on May 22, 2001 11:18 AM
custom format as 0 "text" and only the value will be used
Posted by Michael on May 22, 2001 12:50 PM
must be mssing something, because a custom format using 0 "text" didnt work for me. BTW I am using the 97 version
Posted by Barrie Davidson on May 22, 2001 12:53 PM
Are you getting the value and text from two different cells?
Barrie
Posted by michael on May 22, 2001 1:04 PM
I should have qualified. Text and a number are in the same cell.
Posted by Barrie Davidson on May 22, 2001 1:21 PM
Okay, next question: Is there a convention to the data (i.e., ####aaaaaa)? What I mean is, the number is always four digits or the last six characters are always text.
Barrie
Posted by michael on May 22, 2001 1:25 PM
1st up to six characters are text and last digits are a dollar value. eg text $10
Cheers
Posted by Barrie Davidson on May 22, 2001 1:39 PM
Okay, now we're talking. Is that (text $10) the exact format of your data? The dollar sign is always preceding the numbers? (almost there!!)
Barrie
Posted by Dave Hawley on May 23, 2001 3:09 AM
Hi Micheal
Formatting wont alter the value, it will still be text.
What you probably need is the N Function:
=VALUE(N("Abcd$")&10)
This will show only the 10. The text is converted to a zero value.
..Or if your Text is in cell A1 and the number starts at the dollar symbol you could use:
=VALUE((RIGHT(A1,LEN(A1)-FIND("$",A1))))
Dave
OzGrid Business Applications
Posted by michael on May 25, 2001 8:13 PM
Yes indeed - the $ could always preceed a number
Posted by Barrie Davidson on May 28, 2001 11:09 AM
Mike, for an example, if cell A1 contains "text $10" and you want to use the number 10 from this text string in a calculation, the following formula will extract that number from the string.
=VALUE(RIGHT(A1,LEN(A1)-FIND("$",A1)))
You can then use that for calculations. For example, if you want to take that number and add 5 to it the formula would be
=VALUE(RIGHT(A1,LEN(A1)-FIND("$",A1)))+5
Is this what you are looking for?
Regards,
Barrie
