The backslash means the zero is to be placed at that location as a zero, not
a meta-character representing a number. I think the problem you are having
with the space is because your local setting probably uses the space as a
"thousands separator", so I think Excel is seeing the space and reading it
as number of more than 3 digits need to be separated with the "thousands
separator". Try the formula this way and see if it works (note the backslash
in front of the space)...

A1 = 12345
B1 = TEXT(A1,"###\ ##")
C1 = "Cross road"
D1 = B1 & C1

--
Rick (MVP - Excel)


"Bengt" wrote in message
...
Thanks for your input. I did what you suggested. The formatting code that
appears is "000\00" and when I apply that, Excel's response is "123405".
Is
there a problem with characters here. Should the backslash be some other
character?

I also tried the formatting code "000 00" but that resulted in "12 345"

So I am still confused.

Bengt

"Ron Rosenfeld" skrev:

On Tue, 14 Apr 2009 04:13:01 -0700, Bengt

wrote:

I am trying to concatenate two cells. One of the cells is formatted
using the
"Special" option on Format Cells. The formatting I have been using is
the one
for postal codes, and for Swedish use, the postal code is normally
formatted
as
123 45, i.e. three digits, a space and then two digits.

The formatting applied on the cell when it is by itself works fine. When
I
concatenate the postal code with something else, the formatting doesn't
work
anymore. I suspect that I could use the TEXT-function, and give a
formatting
instruction as the second argument, something like this:
=A1&Text(B1;"format code")

but I couldn't find what format code to use.

Anyone knows?

The way to determine the Format Code is:

After you have selected the Swedish Postnumber code in the Format
Number dialog box, Select the "Custom" Category and use the code
displayed in
the Type: entry box.

On my system, this displays "000 00"

--ron