Concatenate

[rduncan]

I'm trying to use the concatenate formula but it keeps returning a "#value" error. My formula is as follows: =concatenate("K",c12,d7,e9). c12=902, d7=E, e9=1. So concatenate should return K902E1. Any advice?

 

[jim may]

It worked for me. (xl2007)

 

[hiker95]

rduncan,

It works for me:

Excel Workbook
	A	B	C	D	E
1	K902E1
2
3
4
5
6
7				E
8
9					1
10
11
12			902
13
Sheet1

 

[rduncan]

hmm, could it have anything to do with the formatting of the individual cells? should they all be formatted as text? some are numbers and some are text at the moment.

 

[Special-K99]

Try this

http://support.microsoft.com/kb/214311

On Excel 2007 the Transitional Formula Evaluation is under

Excel Options
Advanced
and at the very bottom of the window Lotus Compatibility

If Transition Formula Evaluation is ticked I get an VALUE# error on CONCATENATE
If unticked I get the correct value

 

[rduncan]

that didn't seem to do it either Special-K. there must be something wrong with my sheet as I opened a new workbook and it works fine there. tis a mystery. thanks everyone!

 

[Scott Huish]

BTW, you don't have to type CONCATENATE at all:

="K"&C12&D7&E9