Windows XP: Excel 2002
I am trying to insert a cell reference inside a cell that has a sentence in it.
Example: Cell A1 contains the value "27".
Cell A2 contains the sentence "I am X years old", and I want X to equal the value in Cell A1.
After using the concatenate function many times to combine data from different columns with first names, middle names, and last names, that results in appropriate spacing, I know that I can use the following formula to obtain a full name in one cell, with appropriate spacing.
=concatenate(a1," ",b1," ",c1)
That will give me the contents of cell a1, then a space, then cell b1, then a space, and then cell c1, all in one column (the full name column, in my case).
For my current problem, I've tried things such as
="I am",A1,"years old"
but it doesn't seem to work.
I know I could get around this by putting the first part of the sentence in one cell, and the second half in another cell, and then just reference the 3 cells to get this to work, but then I end up with 2 cells on the spreadsheet that might appear erroneous to my clients, or interfere later on (unless I place them on line 65530 or something, but if you print without making a print area selection, you'll get all those thousands of rows in between as blank pages.
Currently, I get around this by having a cell that says "I am this many years old" and then the next cell has the value, but this is pathetic looking.
Any ideas on how to get a formula to work inside of plain text?
BTW, I did read through the mrexcel articles, and searched the forum, but had no luck finding the answer.
It's sometimes difficult to search for things regarding excel and other computer functions when you don't know the proper terminology. (Searching for "formulas inside text" yielded no results).
I am trying to insert a cell reference inside a cell that has a sentence in it.
Example: Cell A1 contains the value "27".
Cell A2 contains the sentence "I am X years old", and I want X to equal the value in Cell A1.
After using the concatenate function many times to combine data from different columns with first names, middle names, and last names, that results in appropriate spacing, I know that I can use the following formula to obtain a full name in one cell, with appropriate spacing.
=concatenate(a1," ",b1," ",c1)
That will give me the contents of cell a1, then a space, then cell b1, then a space, and then cell c1, all in one column (the full name column, in my case).
For my current problem, I've tried things such as
="I am",A1,"years old"
but it doesn't seem to work.
I know I could get around this by putting the first part of the sentence in one cell, and the second half in another cell, and then just reference the 3 cells to get this to work, but then I end up with 2 cells on the spreadsheet that might appear erroneous to my clients, or interfere later on (unless I place them on line 65530 or something, but if you print without making a print area selection, you'll get all those thousands of rows in between as blank pages.
Currently, I get around this by having a cell that says "I am this many years old" and then the next cell has the value, but this is pathetic looking.
Any ideas on how to get a formula to work inside of plain text?
BTW, I did read through the mrexcel articles, and searched the forum, but had no luck finding the answer.
It's sometimes difficult to search for things regarding excel and other computer functions when you don't know the proper terminology. (Searching for "formulas inside text" yielded no results).
