Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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 --------------- Jacob Skaria "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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
The solution didn't work. Instead of formatting the string "12345" as "123
45", Excel formatted it as "12 345". "Jacob Skaria" skrev: 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 --------------- Jacob Skaria "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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
I have tried this in a new worksheet and it works for me..Are you sure you
have pu the string in formula as "### ##" -- If this post helps click Yes --------------- Jacob Skaria "Bengt" wrote: The solution didn't work. Instead of formatting the string "12345" as "123 45", Excel formatted it as "12 345". "Jacob Skaria" skrev: 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 --------------- Jacob Skaria "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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
Or try pasting this
= TEXT(12345,"### ##") If this post helps click Yes --------------- Jacob Skaria "Bengt" wrote: The solution didn't work. Instead of formatting the string "12345" as "123 45", Excel formatted it as "12 345". "Jacob Skaria" skrev: 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 --------------- Jacob Skaria "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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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" wrote: The solution didn't work. Instead of formatting the string "12345" as "123 45", Excel formatted it as "12 345". "Jacob Skaria" skrev: 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 --------------- Jacob Skaria "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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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: Or try pasting this = TEXT(12345,"### ##") If this post helps click Yes --------------- Jacob Skaria "Bengt" wrote: The solution didn't work. Instead of formatting the string "12345" as "123 45", Excel formatted it as "12 345". "Jacob Skaria" skrev: 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 --------------- Jacob Skaria "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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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 --------------- Jacob Skaria "Bengt" wrote: 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: Or try pasting this = TEXT(12345,"### ##") If this post helps click Yes --------------- Jacob Skaria "Bengt" wrote: The solution didn't work. Instead of formatting the string "12345" as "123 45", Excel formatted it as "12 345". "Jacob Skaria" skrev: 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 --------------- Jacob Skaria "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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
Yes, it works of course, but why doesn't the solution with formatting codes
do the trick? 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 --------------- Jacob Skaria "Bengt" wrote: 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: Or try pasting this = TEXT(12345,"### ##") If this post helps click Yes --------------- Jacob Skaria "Bengt" wrote: The solution didn't work. Instead of formatting the string "12345" as "123 45", Excel formatted it as "12 345". "Jacob Skaria" skrev: 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 --------------- Jacob Skaria "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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
On Tue, 14 Apr 2009 07:06:05 -0700, Bengt
wrote: 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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: On Tue, 14 Apr 2009 07:06:05 -0700, Bengt wrote: 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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. -- Rick (MVP - Excel) "Bengt" wrote in message ... 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: On Tue, 14 Apr 2009 07:06:05 -0700, Bengt wrote: 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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
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: 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. -- Rick (MVP - Excel) "Bengt" wrote in message ... 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: On Tue, 14 Apr 2009 07:06:05 -0700, Bengt wrote: 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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
On Thu, 23 Apr 2009 01:27:01 -0700, Bengt
wrote: 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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatencation and formatting
On Thu, 23 Apr 2009 00:51:02 -0700, Bengt
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions |