Concatenate numbers

[Colin G]

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?

 

[Pete_UK]

Try it like this:

="month "&B4

(shorter to type). Perhaps you had an error in B4.

Hope this helps.

Pete

 

[Colin G]

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!!

 

[Spiky]

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.

 

[Ron Rosenfeld]

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

 

[Colin G]

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

 

[Spiky]

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.

 

[Ron Rosenfeld]

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