Concatencation and formatting

[Bengt]

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?

 

[Jacob Skaria]

Can you please try the below in a fresh worksheet.

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



If this post helps click Yes

 

[Bengt]

The solution didn't work. Instead of formatting the string "12345" as "123
45", Excel formatted it as "12 345".



"Jacob Skaria" skrev:

 

[Jacob Skaria]

I have tried this in a new worksheet and it works for me..Are you sure you
have pu the string in formula as "### ##"

 

[Jacob Skaria]

Or try pasting this

= TEXT(12345,"### ##")

If this post helps click Yes

 

[Francis]

Hi

I have try Jacob's solution and its works
D1 shows 123 45Cross road or

try this using the below example, copy and paste the formula to D1

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


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Bengt]

Unfortunately, the result of your suggestion is the same. "12345" comes back
"12 345". Since "Francis" reports that it worked for him, can this has
something to do with national settings, i.e. that it is language dependent?

Bengt

"Jacob Skaria" skrev:

 

[Jacob Skaria]

Does this work..assuming all post codes are 5 digits

A1 = 12345
B1 = Cross Road
=LEFT(A1,3) & " " & RIGHT(A1,2) & " " & B1

If this post helps click Yes

 

[Bengt]

Yes, it works of course, but why doesn't the solution with formatting codes
do the trick?

Bengt

"Jacob Skaria" skrev:

 

[Ron Rosenfeld]

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

 

[Bengt]

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:

 

[Rick Rothstein]

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

 

[Ron Rosenfeld]

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

1. Very weird to have that backslash. It tells Excel to treat the next
character as a literal, which means it is wanting to put a literal zero in that
space. So I would expect that 12345 would, using that code, display as 123405
(which it does).

2. Perhaps you are not seeing a <space> after the "\". In other words,
perhaps it is really 000\ 00, which would work, and display 123 45.

3. Could there be some issue with the data itself, or with the translation of
the TEXT formula into Swedish? I think the function name is the same, but you
probably need to be using the semi-colon as a separator.

Since the code 000\00 displays, as I would expect it should -- 123405 -- , I
suspect your data is OK.

4. When you select A1, and examine the formula bar at the top, what do you
see?

5. Can you copy/paste the TEXT formula that you are using?

--ron

 

[Bengt]

Thanks very much for your input. I finally got it to work, adding a space
after the backslash. This is what works:

A1="12345"
B1="Crossroads"
C1=TEXT(a1;"000\ 00")&" "&B1

displays as
123 45 Crossroads

I am not absolutely sure that I understand the reason for this behaviour,
but for the time being, I am quite happy that it works.

Thanks again

Bengt

"Ron Rosenfeld" skrev:

 

[Rick Rothstein]

I am not absolutely sure that I understand the reason for this behaviour,

Is the thousands separator set as the space character on your system? If so,
that would probably account for the problem you are having... Excel is
assuming any space (without the backslash "escape" character) means to break
your numbers up using with thousand separators... the backslash in front of
a character tells the formatting function to show that character exactly as
it is, not as the formatting function would normally interpret it.

 

[Bengt]

To find out what the thousands separator really is, I checked the Format
Cells dialog box. To the right of the check box "Use Thousands separator",
you see two parenthesis and nothing between them. Am I correct in
interpreting this as what you have suggested below, namely that the thousands
separator indeed is set to a space?

Bengt
"Rick Rothstein" skrev:

 

[Ron Rosenfeld]

To find out what the thousands separator really is, I checked the Format
Cells dialog box. To the right of the check box "Use Thousands separator",
you see two parenthesis and nothing between them. Am I correct in
interpreting this as what you have suggested below, namely that the thousands
separator indeed is set to a space?

Bengt

That seems likely. The type of Thousands separator is actually set under
Control Panel/Regional and Language settings/Customize/Numbers and look for the
"digit grouping symbol".
--ron

 

[Ron Rosenfeld]

Thanks very much for your input. I finally got it to work, adding a space
after the backslash. This is what works:

A1="12345"
B1="Crossroads"
C1=TEXT(a1;"000\ 00")&" "&B1

Glad to help you figure it out. Thanks for the feedback.
--ron