Concatenate with formatting - finding solutions
How do you concatenate and keep formatting?
1. Click to select cell C1, and then copy and paste formula =A1 & " " & TEXT(B1,"0.00%") into the Formula Bar, and then press the Enter key. You can see two cells are combined and the percentage formatting is kept. Note: B1 is the cell contains the percentage formatting, please change the cell references as you need.
Let's cover a few custom formatting tips from Excel that can save you a ton of time.
In fact, they will help you avoid common spreadsheet mistakes. I'll show them to you first so you have an idea of the power of custom formatting, and then I'll explain the rules behind it. It's actually a very simple rule, if you remember, you did it. (Hip hop music) Here I have some sample data, I have information about compensation for these people.
Alyssa, my boss, comes up to me and says, could you format this so that we have 1,000 separators and new decimal places. So I just have to mark these with the right mouse button, format cells or use the keyboard shortcut, CTRL 1. Here I come to the number, activate 1,000 separators and zero decimal places.
I can also decide how to display negative numbers, and that's it. But let's see, now he comes up to me and says, could you please hide the null values? I don't want to see that em in the report. How would I do this? This could allow me to use custom number formatting.
So let's go back, using CTRL 1 here I get to custom. Now I can see that this is the format that I applied have this. To hide the zeros, I add a semicolon here, copy this, copy this off.
And that was it. And my zero values are gone. Now first, before I explain how to do this, are the null values really gone? You're not really gone, are you? You're still in the cell.
I just can't see it. Remember, number formatting is like a disguise that you are applying to the cell. The actual number after that is what you see here.
So whether I show the decimal places or not show the decimal places, it doesn't change the number behind it. All of this is just a disguise. Now there are a few exceptions, for example with the percentage, which actually shows the percentage format in the formula bar.
But regardless of whether we are using the billing format r another format here, the number after the cell always stays the same, and in this case I just used a custom rule to hide the null values from the view, what is the rule behind it? So let's go back with CTRL 1. The rule to remember is that the first position before the first semicolon determines the display of positive numbers. The second part is how negative values should be displayed.
The third part is how to display null values; and the fourth part is how to display the text. So I entered zero values here for the third part. If I press OK what will I see here? I'll see the word zero value, but what's in the cell is actually a zero, I could use that.
And let's say add five, it understands that this is not text, there is actually a zero in that cell, right? So that's basically all you need to remember, positive, negative, and zero text. As soon as you add your own new custom format to your spreadsheet, it becomes available for you to reuse, you don't have to retype it every time. You can use some of the existing custom formats that you see here to help you write your own format faster.
Now there are two symbols here that grab our attention. One is zero and the other is the pound sign or hash sign. Both are just placeholders for numbers.
The only difference is that the pound sign is a variable placeholder while the zero is a fixed placeholder decimals or here to visually indicate that this is a 1,000 separator. I'll explain this in more detail in a second. One thing I want to show you, what happens when I do this, what happens to these values? to disappear.
But they don't really go away, do they? Because they are still in the cells, they simply disappear from view. If you want to erase the type of format, all you have to do is go back to the general format, which is a disguise eraser. Let's take a closer look at the difference between zero and the hash, or the pound sign, I'm going to highlight this here because there are values behind it, I've just hidden it from view.
I'm going to apply my eraser right here. Here I want some Enter product codes, I want to display them as four digits on my report, I don't really want to change the number here, I just want to change the way it is displayed, so I can highlight this by pressing the Ctrl key under Custom , I will be using the 0000 custom format. This forces this to be four digits and press OK.
That is the difference between the zero and the pound sign. So if I come back here and change this to #### what? I get? I'm getting back what I had because the pound sign is a variable wildcard, while the zero actually forces it to be four digits long in this case. Let's say my boss is telling me could you add some text? Every time we come across a negative number, we mention in the cell that this is 'Returns'.
If I did this wrong. I come here, type in Returns. When I hit Enter, Excel won't like it because the cell starts with a minus number.
So I'm going to say OK, and I'll put this as text, I'm just adding a quotation mark to this, of course if I wanted to put these together this won't be included, but I could make my boss happy and at the same time make my table happy if I did use custom formatting instead. So I never go. To change these to no numbers, I always keep them as numbers.
But I'm going to define a specific custom format and say that if I come across a negative number it should return the word Remember, we're going to start with how to format positive numbers. So let's keep it simple in this case. I'll just keep the number as I have it, then the minus pound sign.
And now I want to say Returns. So I'll just type this in, then I can decide how to format zeros. Well I can just hide it if I want to.
And if I don't specify anything, it will take the positive format for zero. If I do not specify a negative format and only one format, this format will also be used for negatives and zeros. So now I'm getting 120 returns, exactly what my boss wants.
But inside the cell, it's the number my table likes, right? And like my calculations so that I can now add them up without any problems. I'm going to sum this up, I'm going to use the ALT keyboard shortcut is the same, I want to say that this is the total number. The wrong way to do this is to say and add 'total'.
But this is no longer a number. I can't use them in formulas because it's text, right? So I'm going to use custom formatting for that. And how can I do that? I'm going to use a 1,000 separator in this case as these are big numbers and easier to read, add the total to that and that's it and say, OK, now my formulas will work.
Here are some tricks you can use with custom number formatting. Now we have a lot more detail in a blog post that was created by Michael, it explains in much greater detail how custom number formatting works, walks you through some of these icons here, and below you can download an Excel workbook to help you with that The example lead I've made here also gives you a more detailed explanation of what these symbols are. And some examples that you can try for yourself here, details on how to use this file can be found in the blog post.
Basically in this column you can see the number format that is used here, if I change this to this accounting format for example, I go into that cell and hit enter, I can see the current format being applied to it, it saves you some time so that you don't always have to go back to the custom formatting dialog to see what type of format is being used. Also in the second tab here for the Home ribbon formats, you will be guided through the different formats that are available to you here. And what's actually behind those formats interested in learning more about custom formatting, make sure you download this file, go through the blog post and play around with it.
Custom formatting can be super creative. I have a few more articles and Blog posts showing you different ways you can get really creative with them. I added them to the cards in the description of the article.
What about your files? Do you use any wicked custom formatting rules there? Let me in the comments below I hope you enjoyed this article give it a thumbs up, and if you want to improve your skills, consider subscribing to this channel. (light rock music)
How do I merge cells without losing formatting?
- Press Ctrl + F to open the Find and Replace dialog, or click Find & Select > Find.
- On the Find tab, click Options > Format.
- On the Alignment tab, select the Merge cells box under Text control, and click OK.
In this article you will see three ways to merge multiple columns in Excel without losing any data.
Let's look at a general example here I've named parts in different columns, if you need to merge such values try the most obvious way and hit the Merge and Center button, you will see a warning that the cells will be merged and all your data will be erased except what you have in the top left corner. There are several ways to avoid this and to combine the data sets, the easiest and fastest solution would be solution With a special ID and designed for this task, the installation takes a minute and you can find it in the tab abel bits. Let's start it by clicking the cell merge icon It just offers three simple settings, first choose the way you want to merge the selected values, if you want to combine the records in each row, let's select row by row if in doubt, you can check this schematic note which shows how exactly to join the data next Enter the separator for the values I'll choose the default space for the names but you can add any custom space you want in this field Enter characters, then define where to put the results in the left or right column.All of the check boxes below are optional, but I'll show you the flexibility they give if you plan on clearing the original values after combining you can use this option use to delete the merged columns you can use the second field if you want to join the cells in addition to the values i will show you how each cell will look needs two columns if you have some spaces in the range like here there are some authors with only last name you can avoid adding extra separators x ' consecutive by ticking this option choose the line break if your target cells are too small to eventually show the resulting values as you cannot undo the changes made by add-ins.
It's always a good idea to make a backup of your worksheet and get the full names of different columns so the second way you can go is to use the concatenation function. First you need to create an additional column for the formula after adding a header row. Start to enter the equal sign in the next cell Link the brackets and you will see a hint at this point to enter the desired text.Of course, to join our universal solution, this means that we are entering references to the cells that we want to combine, so let's write B to put a comma and then put the separator in quotes for our values, for example to add a space, we put it in quotes put a comma and add the next cell c2 close the bracket and press enter The formula allows you to add as many values as you want, it also gives you the flexibility to use different separators and combine data even from non-adjacent cells, so you can concatenate open brackets b with spaces and quotation marks d, then add a colon between d2 and f2 You drag this formula down the column, or if you have a large blackboard, point to the lower right corner of the cell until you get down.
don't see a cross in place of the mouse pointer and double click on it this will copy the formula into all cells of the column again formula you can use is very simple enter m% between the values and separators enter, for example, the equality reference to the first value of b2 +% a then the quotes separator I want to use a space and the second value and% separator +% value and soon you can leave the formulas to update the results when the records change or you can convert them to values and delete the original columns from your table to do so select your result column you can use the shortcuts press ctrl + shift and down arrow at the same time press ctrl + c or right click on any cell the column to copy the cells, then right-click again and select You have the option to insert special values and click OK, now you can remove the original columns without affecting the results now there is another way to merge data if the columns are next to each other and if you can use the same separator for the values You can use the notepad workaround first you have to select the records and copy them to the notepad open notepad and paste the copied values you can press Ctrl + V to do that you can use the standard exchange dialog here to enter a different separator. Select the character between the pasted values and paste paste the tab character into the search port field and enter the separator that you would rather have to replace it by clicking Replace and close the field Now You just select Paste the records back into Excel and copy them to Notepad.You can press Ctrl + A to select them all and proceed Ctrl to copy them.Copy Switch to your worksheet and paste the results to summarize it can use 3 ways to combine records from multiple columns without losing data.You can use the formulas and then convert the results to values.You can copy your data to the notepad.Replace the separator and see the results again in table or table You can run the Merge Cells add-in, no matter which option you choose, you will get merged values that you c To the Lin k to find the add-in in the description of this article, you can merge cells as a separate add-in or as part of Ultimatesuite with a discount of 15% if you use the coupon code preview you want to clarify and subscribe to our channel, for more Microsoft Excel tips and tricks
How do you concatenate time format in Excel?
- Enter this formula =Concatenate(A3,” “,TEXT(B3,”mm/dd/yyyy”) into a blank cell besides your data.
- Or alternatively can use the second formula as =A4&” “&TEXT(B4,”dd/mm/yyyy”) into a black cell besides your data.
Welcome! In this article tutorial we will see how to combine the contents of two cells, in this case a first name and a family (or last) name - thanks to which we have merged a full name into one cell interesting at? Let's see how it goes! Hello! And welcome to the EasyClick Academy! My name is Frank and along with our EasyClick team we are here to make sure you can use Excel quickly and easily thanks to our easy-to-follow article tutorials I'll show you this short table of data that we do to do all the steps needed to combine names in Excel.
Remember, the size of the table doesn't limit you - you can work with lists for much longer, which saves time and avoids boring copying and pasting of data one by one. Shall we start? First choose where the merged full name should appear, click on the selected cell - we click on cell D3 then click on the fx button at the top here which is for inserting a function. In the pop-up window, select the 'All' category and look for the 'CONNECT' function.
Confirm with OK. The 'JOIN' function can merge up to 255 different elements into a single cell. Excel currently only offers two fields - 'Text 1' and 'Text 2'.
This is where you can combine the data you need. Don't worry if you want to combine more than two items on your list. When you start entering the data, the third, fourth and fifth fields will automatically appear once you finish the last have reached the visible field.
and it will go on like this. In total, you can combine up to 255 different elements in Excel. And so you can enter the first one.
Think about the format of the combined text. We want the merged full name to appear as the first name - a single space - then family name (or last name). Therefore the first name is inserted in the first field 'Text 1'. .
Click the cell that contains the first name, in this case cell B3. Excellent! Since we now need a single space between the first name and the last name, we will enter the space in the 'Text 2' field by simply clicking in the field and pressing the space bar, if we click in the 'Text 3' field, Excel will 'code' the single space in the required form. You can see the space in double quotes.
The same would work for a hyphen, colon, or any other character space in field 2 and go on, click in the 'Text 3' field and enter the second part by selecting cell C3 which contains the family name. If you look at the section just below the text boxes you can see how the text is combined and check out the final form. After you have entered all of the required information, click OK.
Good work! We combined the names from the list without manual rewriting or copying and pasting. If you want to continue and merge the rest of the names on the list, you'll need to recreate the function and reference to the data on each line. Just hover over the cell that contains the function and drag the lower right corner of the cell to fill the remaining rows with the same formula name on the list.
So. And just before we close it, there is one more thing to consider. If you need to change the information in the list, you need to do so in the source cells that the formula refers to.
Click on any cell with the combined names and you will see that this target cell does not contain the text itself, in this case the full name, but the function and reference to other cells in the table, along with a single space, which we put in the second field, so if you need to change any concatenated data, you have to go to the source cell first and rewrite the information there. For example, if we need to change Tommy to John we will change the text in cell B3, the change will be reflected in cell D3 which has the combined names, if you found this tutorial helpful, give us a Like and check out others Join the EasyClick Academy article tutorials. Learn quickly and easily how to use Excel away! Is this your first time on EasyClick? We look forward to welcoming you to our online community.
Click the Subscribe button and join the EasyClickers! Thanks for watching and see you in the next article!
How can I combine text and numbers in a cell and keep the number formatting?
- Select the cell in which you want the combined data.
- Type the formula, with text inside double quotes. For example: ="Due in " & A3 & " days" NOTE: To separate the text strings from the numbers, end or begin the text string with a space.
- Press Enter to complete the formula.
Hello, this is Gary from MacMost.com.
Today let's take a look at cell formatting in Numbers, MacMost is brought to you thanks to a great group of 700+ backers. Go to MacMost.com/patreon.
You can read more about the Patreon campaign there. Take part and secure exclusive content and course discounts. Every cell in a Numbers table has a value.
How this value is displayed depends on the cell formatting. There are different types. Here I have some sample cells.
When I select one of them and go to Format, Cell, I have set it to Data Format, Automatic. It tries to look at the data in the cell and format it automatically. A number is only displayed here as a number.
A date is displayed as a date. You can always look at the bottom left here to see the actual value. I could see here that this is 20 and this is August 19th, this is August 19th at nine o'clock, this is point five, this is six days, that means six days.
It's a duration. If it were text, it would appear as text instead of a value. Instead of using Automatic, you can customize the format here.
So let's click Data Format and let's say we want to format this as a number. This doesn't seem to change. But now we have options here.
We can have multiple decimal places. So it's set to Automatic, but I could say I always want to force two decimal places. I could do the same with a number like this too and set that to Number and force it to zero decimal places and you could see it rounded to 6 even though the actual value shown below is 5.7 around negative values different ways to display.
So here is a negative value with a hyphen. You can enter a negative value as red or in brackets or brackets and red. You can also add a thousand separator.
That's definitely something I love to do in numbers like this. You switch to the number format, add a thousand separator. You will get a comma between every three digits there, at least in the United States.
Now you can set the format to currency instead of number. You have similar options here in terms of decimals and the representation of negatives. They also have the thousand separator.
But you can also select a currency and change it to something. For example, you can have dollars or euros. You can switch to an accounting style that puts the currency symbol all the way to the left.
Now if you use percent it will take a number like 0.5 and display it as 50%. This could be a little tricky because a number, say 20, and you change that to a percentage, it shows it as 2000% which is correct since a number is 100%.
You can also choose to display something as a fraction. So here I have 0.5.
Put that on fraction and you could see it's a slash two. In other words ½. I could set the precision to one digit, two digits, three digits, or just go to things like halves, quarters, eight, etc.
For that I could change this to a fraction and you can see it says five and seven tenths. I could switch it to hundredths and it will show me seventy hundredths. I can let it go to quarter and it can go to five and three quarters the next.
Now you can also choose Numerical Systems, although not many people outside of programmers use them. So here I could go to the Numerical System for 255 and say I want this to be base 16 and now it's represented in hexadecimal. You can also use scientific notation here.
This makes that number eight hundred and seventy million a little easier to read, especially if you are recording scientific data here. Now you can also set the number of decimal places. So you can have many different numbers with the same number of decimal places.
Formatting works for things like dates too. So let's set the date and time instead of Automatic. Now that it's set that way, I can choose a specific format here.
I could go with such a long format. Something short. I might or might not include the year in many different formats.
Or just show the month or just the year. You can also include the time value. Any cell that contains a date or a time has both.
So this is August 19th and that means August 19th at 9:00 a.m. But if I were to switch that to something over time, it would work and put the time in there.
It will be noon now. In this case, when I entered the value, I added an actual time so that a time value is entered here and I can choose one of them. You also have the duration.
So you can switch this to permanent instead of automatic. You can use automatic units and have things like the letter which is the actual value. So 6d is six days.
But I can force it to show 6 space days if I want to see this format. But I can also go to custom units here and decide exactly what is shown. For example, I can display days and hours and it shows six days and zero hours even if there are no hours recorded.
I can add minutes, seconds, etc. Then I can choose a style for it. So I can only use the letter instead of the whole word for each unit.
If you work with numbers, if none of these options work for you, there is always the option to switch to a custom format. So choose Create Custom Format. It brings all this control up here.
You can name your custom format. We'll stick with the default name here, but with a custom number format, date and time, or text. So with Number we can format it like this and you can drag and drop these things into the custom format here.
For example, I could add a scale like K. So for this number here I can make a custom format and I could say use the default formatting here, drag K up and now you could see that it is 870K. But I could click Edit Custom Size and change that to M for Millions and you could see 870M.
There's a lot more you could do here. I could use real words, hundreds, thousands, millions instead of C, K, or M. I could add a currency value before that and select the currency value.
I could add decimal places directly. Set the number of digits. For the number itself I click there and there is a lot that I could adjust.
For example, I could show or hide the separator. I could show zeros for unused digits. I could add digits, remove digits, set the number of digits.
I could add a rule too. So when you click here to add a rule, the format may change depending on the value in the cell. For example, I could say that if the number is less than zero, then put that element in brackets here.
Now, if I prepend negatives here, you can see those brackets go there as this is part of my custom format. The rules can be anything. They can be if it is the same, if it is smaller than, if it is larger than.
So you can use this as an alternative to conditional highlighting. For example, I can set a custom format here to add a rule and say that if it equals 42, there will be some exclamation marks after it. Now you won't see any change there.
But if I change that number to 42 you can see that it is. I could easily reuse these custom formats. So let's put a bunch of numbers in here and I could select those cells and change the format to custom format 1 and you could see the number with 42 has these exclamation marks after it.
So play around with the custom formatting to see all the different things you could dowith Rules.You could use custom formatting for dates too. So here are some complex controls that give you precise control over how each element is displayed.
You can drag and drop these here. You can add text between things. So if I wanted to create a format that was basically year and then hyphen and then month and that with leading zeros and a hyphen, then set up the day of month and that with leading zeros, I can get a format that looks like this .
You can even set a custom format for text. So I can go and make a custom format and then all I can have here is the actual text itself. But I could put something in front of it.
So I could have a text like that. Put in a little space there and you can see it says Name Colon Space text. But the real value is just text.
I could double click here and type something else and you could now see it come up with that custom formatting. So there is a lot you could do with regular formatting and a lot with CustomFormatting. Go ahead and make a sample number document and play around with each of them and see what they can do so you know how they work the next time you need them.
How to retain the number format in the concatenate formula?
To retain the number format in the CONCATENATE formula, as I have already mentioned, you should use the TEXT function. There are different custom formats that you can use in the TEXT function. But I am only picking the most useful ones. First, see the custom number formats that you may be required in Concantenate.
How to use concatenate and text function in Excel?
You can use a formula based on the CONCATENATE function and the TEXT function to achieve the result. Like this: Type this formula into a blank cell and press Enter key. And then drag the AutoFill handle over to other cells to apply this formula. The “Format Code” can be used in the excel Text function are shown in the below table.
Do you lose formatting when you concatenate cells?
I want to concatenate different cells. Two of the cells I concatenate have percentages, one showing a negative percentage in red. I seem to lose the formatting when I concatenate. Is there any way to hold it? This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread. * Please try a lower page number.
How to use concatenate to get the same color as the cell?
With the function concatenate, how can we. A) Ask to get the same format as the original cell. B) Force a color format, like red. Exemple = concatenate(A1; ' '; A2; ' '; A3) I would like that. 1) A1 displays the same color and number format as the cell A1 (for example. green with number #'##0.00. 2) Force the format of A2 in red.