Text to Formula?

  • Thread starter Thread starter John Wheeler
  • Start date Start date
J

John Wheeler

hi,



I've made several formulas to help me concatenate some text. I didn't use
the concatenate formula for various reasons. However, I have solved my
problem by using a formula to develop a text string such as =A1&B1&C1.
However this just shows up as text in a cell. I can make it a formula
manually by entering the cell and hitting a backspace before the = sign and
hitting enter, however I'd like to know if there is a way to make this
string of text a real formula without manual intervention such as by
changing the format of the cell or applying an additional function etc. I
have hundreds of these string so I like to automate this.
 
I have hundreds of these string so I like to automate this.

Dunno if you'd consider this qualifies as "automate" ...

(Back up first). Select them all. For non-adjacent ones, use CTRL (or you
may get some joy with F5 -- Special -- Constants -- Text only.

Hit F2, press CTRL+Enter.

HTH,
Andy
 
Just signed up for this group and first thread I see concerns answer I want.
I have text columns that need to be concatenated, this is easy I use =A1&"
"&B1, then copy down. Problem is B column is in italic text, and the
concatenation loses the italic. Is there a work around. Thanks
Dave Cohen
 
Problem is B column is in italic text, and the
concatenation loses the italic. Is there a work around. Thanks
Click to expand...

Nope, cell formatting would not come across with the concatenation.
 
Hi Dave,
If you copy down with the fill handle both the formula and the
formatting will be copied and the formula will be adjusted accordingly.
http://www.mvps.org/dmcritchie/excel/fillhand.htm

When you say text columns what do you mean, because
Format of General will use formulas, but a Format of Text
would show the formulas themselves. Changing between
Text and numbers will have no effect until the content is
reentered. Changing format of numbers to another number
format is effective immediately.
 
Format the cells as General.
Edit|Replace
=
with
=

Excel will reparse your entry and see that's it's a formula.
 
Thanks but it doesn't seem to work. It works ok if you got cell by cell and
do it but if you try to do a range of cells it seems to truncate the formula
i.e. it only takes seems to take the first 20 characters in the string.
 
Dave,

I understand what concatenation is I was referring to the concatenation
function, I didn't use the function as it was limited to 30 cells. I do not
have spaces in front of the equal sign and the trim function doesn't
convert the text to formula. I don't have a formula I have text that I want
to behave like a formula without addressing each cell individually. Thanks
for the proposal anyway.
 
Thanks Dave it worked!
Dave Peterson said:
Format the cells as General.
Edit|Replace
=
with
=

Excel will reparse your entry and see that's it's a formula.
Click to expand...
 
Back
Top