1. ## Link to a value in a text string (2000)

Assume in cell A1 I insert the text: "This Study Utilizes 10 Variables". What I would like to do is be able to "transform" the "10" in this text string into a number that can in turn be referenced by one or more formulas in the same (or different) worksheets. Any help?

2. ## Re: Link to a value in a text string (2000)

Is the text (apart from the specific number) fixed, or will it vary?

3. ## Re: Link to a value in a text string (2000)

I suppose fixed. What do you mean by "vary"?

4. ## Re: Link to a value in a text string (2000)

By "fixed", I meant that the text will be like this:

"This Study Utilizes 10 Variables"
"This Study Utilizes 37 Variables"
"This Study Utilizes 475,456 Variables"

By "vary", I meant that the text will be like this:

"This Study Utilizes 10 Variables"
"There are 50 states in the USA"
"The temperature is 70 F"

5. ## Re: Link to a value in a text string (2000)

The text will be fixed.
Thanks for the clarification...

6. ## Re: Link to a value in a text string (2000)

Possibility 1: enter the following formula in B1:

=VALUE(MID(A1,21,FIND(" ",A1,21)-21))

or

=MID(A1,21,FIND(" ",A1,21)-21)*1

The formulas start looking at the 21st character (the beginning of the number) and take everything until the next space, anc convert to a number. You can use B1 for calculations in other formulas.

Possibility 2: format A1 with a custom format

"This Study Utilizes "0" Variables"

and just enter the number 10 in A1. It will be displayed as text, but stored as a number, so you can use A1 directly in calculations.

7. ## Re: Link to a value in a text string (2000)

Very nice, Hans. Thanks!

8. ## Re: Link to a value in a text string (2000)

Another solution in addition to Hans' solution would be to put the number in a separate cell. If you put the number in cell A1, then you could put the following formula in the cell where you want the text:

="This Study Utilizes "&A1&" Variables".

Then you can refer to cell A1 anyplace where you need to use the number. This also makes it easier to change the number.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•