Concatencation and formatting

  • Thread starter Thread starter Bengt
  • Start date Start date
B

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?
 
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
 
The solution didn't work. Instead of formatting the string "12345" as "123
45", Excel formatted it as "12 345".



"Jacob Skaria" skrev:
 
I have tried this in a new worksheet and it works for me..Are you sure you
have pu the string in formula as "### ##"
 
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
 
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:
 
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
 
Yes, it works of course, but why doesn't the solution with formatting codes
do the trick?

Bengt

"Jacob Skaria" skrev:
 
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?
Click to expand...

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
 
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:
 
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
 
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
Click to expand...

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
 
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:
 
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.
 
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:
 
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
Click to expand...

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
 
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
Click to expand...

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