Using " Concatenate"

[Pauljj]

I am using the Concatenate funtion to combine 3 columns of text, however 1 column has a number in it, which produces a VALUE error. Is there a way to convert a number to text. If I put in an apostrophe, this does work but I have 200 rows and dont want to have to insert an apostrophe before every cell ???

 

[GlennUK]

Please show your formula, and the values, that are causing the VALUE error, as Concatenate shouldn't cause an error that way. ( by the way, I've seen people using "+" instead of "&" for concatenate ... you wouldn't be doing that would you? )

 

[Pauljj]

I started to the & then changed it to use the CONTACENATE function, here is my example

hotels.wk4
	B	C	D	E	F
2171	Escondido,CA	515WEST4THAVENUE	Escondido,CA	92025	#VALUE!
2172					#VALUE!
2173	Southfield	23859TELEGRAPHROAD	Southfield	48034-4105	23859TelegraphRoad,Southfield,48034-4105
2174	RoanokeIntown	1411COULTERDRNW	RoanokeIntown	24018	#VALUE!
hotels

 

[Richard Schollar]

Hi Paul

Do you have any rounding on the C column?

Richard

 

[Pauljj]

No, its a zip code?

 

[Richard Schollar]

And it's not the result of a formula?

The reason I ask is that you shouldn't be getting the #VALUE (I don't on my machine).

Try this as a formula copied down:

=A1&B1&TEXT(C1,"0")

Richard

 

[Pauljj]

Perfect, thanks so much

Paul