Text to Formula?

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.
 
A

Andy Brown

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
 
D

Dave Cohen

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
 
M

Mark Henri

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.
 
D

David McRitchie

Hi John,
You have a space in front of the equal sign as you indicated by
using the backspace before the equal sign to fix. You can
simply fix the formula and then use the fill handle to copy down.

Another way of fixing the problem is with a macro. The TrimALL
macro will remove leading and trailing spaces and will fix your
problem. Directions to install/use a macro on my getstarted.htm

Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm#trimall.

There is a period at the end of your formula, probably meant as the
end of sentence in your post but you should be aware of such things
when you post for when people try to copy and paste..

BTW, =A1&B1&C1 is also called concatenation and is used in
many languages . In other words concatenation
does not have to involve the CONCATENATE Worksheet Function.
 
D

David McRitchie

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.
 
D

Dave Peterson

Format the cells as General.
Edit|Replace
=
with
=

Excel will reparse your entry and see that's it's a formula.
 
J

John Wheeler

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.
 
J

John Wheeler

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.

David McRitchie said:
Hi John,
You have a space in front of the equal sign as you indicated by
using the backspace before the equal sign to fix. You can
simply fix the formula and then use the fill handle to copy down.

Another way of fixing the problem is with a macro. The TrimALL
macro will remove leading and trailing spaces and will fix your
problem. Directions to install/use a macro on my getstarted.htm

Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm#trimall.

There is a period at the end of your formula, probably meant as the
end of sentence in your post but you should be aware of such things
when you post for when people try to copy and paste..

BTW, =A1&B1&C1 is also called concatenation and is used in
many languages . In other words concatenation
does not have to involve the CONCATENATE Worksheet Function.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

John Wheeler said:
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.
Click to expand...
Click to expand...
 
J

John Wheeler

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...
 
Top