Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    Very nice, Hans. Thanks!

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

Posting Permissions

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