Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers
I want to be able to insert a phrase such as Month 3 where the month number
is entered in a different cell (say B4). I have therefore entered formula - concatenate("month ",B4) and it returns a #value! error. Both cells have general format. When I do this on a brand new spreadsheet i don't have a problem in that it displays as Month 3 so there must be a problem on the spreadsheet i am using which is causing this. Does anybody have any clues? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers
Try it like this:
="month "&B4 (shorter to type). Perhaps you had an error in B4. Hope this helps. Pete On May 2, 1:08*pm, Colin G wrote: I want to be able to insert a phrase such as Month 3 where the month number is entered in a different cell (say B4). I have therefore entered formula - concatenate("month ",B4) and it returns a #value! error. Both cells have general format. When I do this on a brand new spreadsheet i don't have a problem in that it displays as Month 3 so there must be a problem on the spreadsheet i am using which is causing this. Does anybody have any clues? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers
Thanks Pete, I did try it like that as well but had exactly the same outcome.
Equally, when trying it like that on a new spreadsheet it worked!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers
On May 2, 7:51 am, Colin G wrote:
Thanks Pete, I did try it like that as well but had exactly the same outcome. Equally, when trying it like that on a new spreadsheet it worked!! Well, a #VALUE! error means something it is trying to calculate is text instead of a value. But your CONCATENATE formula (either version) ought to still work, even if B4 is text. Since it is a text function. Was the old file created in an older version of Excel? There are all sorts of little glitches in old files that pop up after you upgrade. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers
On Fri, 2 May 2008 05:08:00 -0700, Colin G
wrote: I want to be able to insert a phrase such as Month 3 where the month number is entered in a different cell (say B4). I have therefore entered formula - concatenate("month ",B4) and it returns a #value! error. Both cells have general format. When I do this on a brand new spreadsheet i don't have a problem in that it displays as Month 3 so there must be a problem on the spreadsheet i am using which is causing this. Does anybody have any clues? What is the real formula in the cell? (Copy it and then paste it into a messge). What is the actual contents of B4 -- is it just the number two, or is there a formula there? --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers
Ron, it is just a number not a formula. Spiky, the spreadsheet does originate from an earlier version (in fact, I think it was originally a lotus one!) so I guess that is the answer. Not really a problem in as much as it is just one extra item to remember to change but would have been nice to have automated it. Maybe I need to spend a month or so totally rewriting the whole thing. Thanks to you all for comments |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers
On May 6, 6:29 am, Colin G wrote:
Ron, it is just a number not a formula. Spiky, the spreadsheet does originate from an earlier version (in fact, I think it was originally a lotus one!) so I guess that is the answer. Not really a problem in as much as it is just one extra item to remember to change but would have been nice to have automated it. Maybe I need to spend a month or so totally rewriting the whole thing. Thanks to you all for comments All you should need to do is make a new workbook with the same number of sheets. Name the sheets how you want. Then do a Select All on each sheet in the original file, Copy, Paste to the new book. You'd be surprised how many stupid little things this cleans up. And almost everything transfers...functions, formatting, etc. Print areas and setups is the one thing you'd have to redo. Sometimes it doesn't fix everything, then I usually do a bit more specific copying, which takes longer. Like copy/paste values where possible and then formatting and functions separate, it kinda depends on what the error is. Or totally rewrite the whole thing. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers
On Tue, 6 May 2008 04:29:01 -0700, Colin G
wrote: Ron, it is just a number not a formula. Spiky, the spreadsheet does originate from an earlier version (in fact, I think it was originally a lotus one!) so I guess that is the answer. Not really a problem in as much as it is just one extra item to remember to change but would have been nice to have automated it. Maybe I need to spend a month or so totally rewriting the whole thing. Thanks to you all for comments If the original sheet was from Lotus, that is, indeed, the problem. Find the Lotus compatibility settings (it's on one of the tabs under Tools/Options in pre-2007; and under Excel Options/Advanced in 2007) and DEselect Transition Formula Evaluation (and any other Lotus options you may not want). --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I concatenate custom formatted numbers | Excel Worksheet Functions | |||
VBA to concatenate Text and/or Numbers and/or dates etc | Excel Discussion (Misc queries) | |||
Format numbers / Concatenate | Excel Discussion (Misc queries) | |||
concatenate with numbers | Excel Discussion (Misc queries) | |||
Concatenate text and numbers? | Excel Worksheet Functions |