Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation in Excel 2003
I used to routinely use the concatenate formula in excel, with either syntax:
=CONCATENATE(A1,B1,C1) -OR- =A1&B1&C1 Lately, sporadically, it just doesn't work, leaves my cell sitting with a literal that looks like the formula rather than showing the concatenated value. I don't have the Tool/Options/View Formula option checked, nor have I used the Ctl-` command to turn this on or off. The only way I seem to be able to get around this is by copying a concatenated formula option out of help and pasting it, and then editing it to suit my situation, the Insert Function does not work, either. I HAVE checked my formulas VERY CAREFULLY for misplaced spaces or commas, etc., and I can't find any typos. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation in Excel 2003
There are two potential reasons:
1. the cell has been accidentally formatted as Text. Reformat to General, touch F2, touch ENTER 2. There may be a non-printing character before the = sign -- Gary''s Student - gsnu200755 "lireland" wrote: I used to routinely use the concatenate formula in excel, with either syntax: =CONCATENATE(A1,B1,C1) -OR- =A1&B1&C1 Lately, sporadically, it just doesn't work, leaves my cell sitting with a literal that looks like the formula rather than showing the concatenated value. I don't have the Tool/Options/View Formula option checked, nor have I used the Ctl-` command to turn this on or off. The only way I seem to be able to get around this is by copying a concatenated formula option out of help and pasting it, and then editing it to suit my situation, the Insert Function does not work, either. I HAVE checked my formulas VERY CAREFULLY for misplaced spaces or commas, etc., and I can't find any typos. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation in Excel 2003
Try formatting the cell as General (or anything but Text), then with that cell
selected, hit F2, then Enter. lireland wrote: I used to routinely use the concatenate formula in excel, with either syntax: =CONCATENATE(A1,B1,C1) -OR- =A1&B1&C1 Lately, sporadically, it just doesn't work, leaves my cell sitting with a literal that looks like the formula rather than showing the concatenated value. I don't have the Tool/Options/View Formula option checked, nor have I used the Ctl-` command to turn this on or off. The only way I seem to be able to get around this is by copying a concatenated formula option out of help and pasting it, and then editing it to suit my situation, the Insert Function does not work, either. I HAVE checked my formulas VERY CAREFULLY for misplaced spaces or commas, etc., and I can't find any typos. Any suggestions? -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation in Excel 2003
I just tried doing this, as you suggested, and it DOES work, but it's a bit
peculiar. If the formula's sitting in a text-formatted cell and I reformat to general, it doesn't change, but if I F2 edit the formula, and back up past the = sign and then hit backspace, it will change (like I'm deleting what's not there). Kind of seems like they're stuffing in an invisible ' symbol, like they used to use for text left-justification. But in the old days, the ' left, " right, and ^ center used to be visible symbols if you looked at the formula line, and they aren't visible here anymore. I can't completely test this right now, since I'll have to wait until it happens to me involuntarily again. But I think you've likely pegged it for me. It surprises me, though, that I fixed it originally by pasting in text, seems like that would have been more likely to be formatted as text rather than general. So strange. Anyway, thanks for your help! Much appreciated! "Gary''s Student" wrote: There are two potential reasons: 1. the cell has been accidentally formatted as Text. Reformat to General, touch F2, touch ENTER 2. There may be a non-printing character before the = sign -- Gary''s Student - gsnu200755 "lireland" wrote: I used to routinely use the concatenate formula in excel, with either syntax: =CONCATENATE(A1,B1,C1) -OR- =A1&B1&C1 Lately, sporadically, it just doesn't work, leaves my cell sitting with a literal that looks like the formula rather than showing the concatenated value. I don't have the Tool/Options/View Formula option checked, nor have I used the Ctl-` command to turn this on or off. The only way I seem to be able to get around this is by copying a concatenated formula option out of help and pasting it, and then editing it to suit my situation, the Insert Function does not work, either. I HAVE checked my formulas VERY CAREFULLY for misplaced spaces or commas, etc., and I can't find any typos. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation in Excel 2003
Dave,
Thanks for your reply. I just got finished answering "Gary's student" more extensively (above), and I think you're both right. I don't know how I came to have those cells formatted as text, unless there's some shortcut to do that that I hit accidentally. I'll have to wait until it comes up again sporadically to test if that's what's always happening, but I suspect you're both right. Thanks again! "Dave Peterson" wrote: Try formatting the cell as General (or anything but Text), then with that cell selected, hit F2, then Enter. lireland wrote: I used to routinely use the concatenate formula in excel, with either syntax: =CONCATENATE(A1,B1,C1) -OR- =A1&B1&C1 Lately, sporadically, it just doesn't work, leaves my cell sitting with a literal that looks like the formula rather than showing the concatenated value. I don't have the Tool/Options/View Formula option checked, nor have I used the Ctl-` command to turn this on or off. The only way I seem to be able to get around this is by copying a concatenated formula option out of help and pasting it, and then editing it to suit my situation, the Insert Function does not work, either. I HAVE checked my formulas VERY CAREFULLY for misplaced spaces or commas, etc., and I can't find any typos. Any suggestions? -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation in Excel 2003
Saved from a previous post.
Excel likes to help. Try this on a test worksheet. Select A1 and hit ctrl-; (to put the date in the cell) now select B1 and type: =a1 Notice that excel changed the format of B1 to match the format in A1. Now format D1 as Text. put ASDF in D1 put =D1 in E1 You see ASDF. With E1 selected, hit the F2 key and then enter (to pretend that you're changing the formula). Excel has "helped" you by changing that cell's format to text. I don't know of any way of changing this behavior. I just select the cell, and reformat it to General (or whatever I wanted). I hit F2 and then enter (to reenter that formula). Sometimes this feature is nice, sometimes it ain't. lireland wrote: Dave, Thanks for your reply. I just got finished answering "Gary's student" more extensively (above), and I think you're both right. I don't know how I came to have those cells formatted as text, unless there's some shortcut to do that that I hit accidentally. I'll have to wait until it comes up again sporadically to test if that's what's always happening, but I suspect you're both right. Thanks again! "Dave Peterson" wrote: Try formatting the cell as General (or anything but Text), then with that cell selected, hit F2, then Enter. lireland wrote: I used to routinely use the concatenate formula in excel, with either syntax: =CONCATENATE(A1,B1,C1) -OR- =A1&B1&C1 Lately, sporadically, it just doesn't work, leaves my cell sitting with a literal that looks like the formula rather than showing the concatenated value. I don't have the Tool/Options/View Formula option checked, nor have I used the Ctl-` command to turn this on or off. The only way I seem to be able to get around this is by copying a concatenated formula option out of help and pasting it, and then editing it to suit my situation, the Insert Function does not work, either. I HAVE checked my formulas VERY CAREFULLY for misplaced spaces or commas, etc., and I can't find any typos. Any suggestions? -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation in Excel 2003
Dave,
I think you're right, this must be what was causing the original behavior. And while I can't recall the exact scenario in which it happened to me before, almost all the reports I do in excel import external data sources. Quite often, when the imported fields are numerics, they were stored as alpha in the original data source, especially when they're foreign keys in the RDB. They import left justified with the little green warning triangle on the cell corner. So it's quite likely that I came across this "helpfulness" from the context, without actively reformatting anything myself. Thanks for helping me convince myself I'm not crazy - and letting me enjoy having my little propeller head spinner whizz for a bit. Always good to perk up an afternoon! ;-D Linda "Dave Peterson" wrote: Saved from a previous post. Excel likes to help. Try this on a test worksheet. Select A1 and hit ctrl-; (to put the date in the cell) now select B1 and type: =a1 Notice that excel changed the format of B1 to match the format in A1. Now format D1 as Text. put ASDF in D1 put =D1 in E1 You see ASDF. With E1 selected, hit the F2 key and then enter (to pretend that you're changing the formula). Excel has "helped" you by changing that cell's format to text. I don't know of any way of changing this behavior. I just select the cell, and reformat it to General (or whatever I wanted). I hit F2 and then enter (to reenter that formula). Sometimes this feature is nice, sometimes it ain't. lireland wrote: Dave, Thanks for your reply. I just got finished answering "Gary's student" more extensively (above), and I think you're both right. I don't know how I came to have those cells formatted as text, unless there's some shortcut to do that that I hit accidentally. I'll have to wait until it comes up again sporadically to test if that's what's always happening, but I suspect you're both right. Thanks again! "Dave Peterson" wrote: Try formatting the cell as General (or anything but Text), then with that cell selected, hit F2, then Enter. lireland wrote: I used to routinely use the concatenate formula in excel, with either syntax: =CONCATENATE(A1,B1,C1) -OR- =A1&B1&C1 Lately, sporadically, it just doesn't work, leaves my cell sitting with a literal that looks like the formula rather than showing the concatenated value. I don't have the Tool/Options/View Formula option checked, nor have I used the Ctl-` command to turn this on or off. The only way I seem to be able to get around this is by copying a concatenated formula option out of help and pasting it, and then editing it to suit my situation, the Insert Function does not work, either. I HAVE checked my formulas VERY CAREFULLY for misplaced spaces or commas, etc., and I can't find any typos. Any suggestions? -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenation in Excel 2003
I may have convinced you that you're not crazy -- but just about this one
subject!!!! <gd&r <grin, duck and run lireland wrote: Dave, I think you're right, this must be what was causing the original behavior. And while I can't recall the exact scenario in which it happened to me before, almost all the reports I do in excel import external data sources. Quite often, when the imported fields are numerics, they were stored as alpha in the original data source, especially when they're foreign keys in the RDB. They import left justified with the little green warning triangle on the cell corner. So it's quite likely that I came across this "helpfulness" from the context, without actively reformatting anything myself. Thanks for helping me convince myself I'm not crazy - and letting me enjoy having my little propeller head spinner whizz for a bit. Always good to perk up an afternoon! ;-D Linda "Dave Peterson" wrote: Saved from a previous post. Excel likes to help. Try this on a test worksheet. Select A1 and hit ctrl-; (to put the date in the cell) now select B1 and type: =a1 Notice that excel changed the format of B1 to match the format in A1. Now format D1 as Text. put ASDF in D1 put =D1 in E1 You see ASDF. With E1 selected, hit the F2 key and then enter (to pretend that you're changing the formula). Excel has "helped" you by changing that cell's format to text. I don't know of any way of changing this behavior. I just select the cell, and reformat it to General (or whatever I wanted). I hit F2 and then enter (to reenter that formula). Sometimes this feature is nice, sometimes it ain't. lireland wrote: Dave, Thanks for your reply. I just got finished answering "Gary's student" more extensively (above), and I think you're both right. I don't know how I came to have those cells formatted as text, unless there's some shortcut to do that that I hit accidentally. I'll have to wait until it comes up again sporadically to test if that's what's always happening, but I suspect you're both right. Thanks again! "Dave Peterson" wrote: Try formatting the cell as General (or anything but Text), then with that cell selected, hit F2, then Enter. lireland wrote: I used to routinely use the concatenate formula in excel, with either syntax: =CONCATENATE(A1,B1,C1) -OR- =A1&B1&C1 Lately, sporadically, it just doesn't work, leaves my cell sitting with a literal that looks like the formula rather than showing the concatenated value. I don't have the Tool/Options/View Formula option checked, nor have I used the Ctl-` command to turn this on or off. The only way I seem to be able to get around this is by copying a concatenated formula option out of help and pasting it, and then editing it to suit my situation, the Insert Function does not work, either. I HAVE checked my formulas VERY CAREFULLY for misplaced spaces or commas, etc., and I can't find any typos. Any suggestions? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Concatenation | Excel Worksheet Functions | |||
Help with Concatenation | Excel Worksheet Functions | |||
concatenation | Excel Discussion (Misc queries) | |||
Excel needs to expand text concatenation capability | Excel Worksheet Functions | |||
Concatenation Help | Excel Worksheet Functions |