Easy Excel - Combining Cells - Concatenation (10) 

Hey Ellen, sorry for the delay, glad you were able to find a solution!

Glad this was helpful :)

Glad it was helpful :)

Glad it was helpful :)

Glad you found it useful!

Glad this was helpful :)

You should copy that new column and paste it as text only. If you copy it, right click, you'll see options to "paste as value" or something like that. If you do that, then you can remove the original cells. Otherwise you'll get a reference error.

+Allen Anderson Glad you found it useful :)

Assuming your data is in A1, B1, and C1, you can do something like this: =A1&", "&B1&", "&C1 That'll add a comma and a space, if you don't want the space just get rid of it in the quotes. Hope that helps!

Glad this old video is still helping people!

If you want to merge the cells, the easiest thing to do would be copy all of your concatenated data, and then paste as value (right click -> paste as value). That will get rid of the concatenation formula but your data will remain unaffected.

Good question! You can concatenate those around the last name. So if first name is in cell A1 and last name is in cell B1, you can do something like this: =A1 & " (" & B1 & ")" There's a space before the first open parenthesis. Hope this helps!

By pairing two cells, do you mean, the value of one determines the value of another? E.g. if cell A1 is "Bob" then cell "B2" will do a lookup and put the value associated with Bob? If so, you're looking for a lookup formula. There are a few types, the most common is vlookup. The second is Index/Match - it's a bit harder but much better than vlookup. And then there's xlookup which is new and better than the previous two but not every version of Excel has it. I think only the latest version and O365 have it. I have tutorials on all three linked below. The pages also have a link to the examples I use in the video at the bottom of the page: VLOOKUP: www.easyprogramming.net/exceltutorials/vlookup_function.php INDEX/MATCH: www.easyprogramming.net/exceltutorials/index_match_function.php XLOOKUP: www.easyprogramming.net/exceltutorials/xlookup.php Also have a bigger example that turns excel into a type of "order form": www.easyprogramming.net/exceltutorials/create_simple_order_form.php Hope this helps! You can find more info on the above three formulas with a quick google search.

Thank you! I think the kind of language you want to learn depends on what you want to do. For beginners, I usually recommend starting off with JavaScript because it's really easy to get started. You don't need any kind of IDE and can just run it on your browser as-is. I also have 56 tutorials on JS here: www.easyprogramming.net/javascript.php Python is also a popular language to start with, especially if you are into data science. I'd also recommend reading up on algorithms and data structures in general as they will be helpful down the line. Once you learn one language, it's pretty easy to move onto others. And it's also okay to start a language and move onto something else if you don't like it. Best of luck!

What's the udemy add?

What does the formula in your first cell look like? And you're using the autofill dot in the corner, right? Not sure where you're seeing a dropdown arrow.

If I understand correctly then you can do something like this = "(" & B1 & ")" If B1 value is RU-vid then the output for that would look like this: (RU-vid)

Hey Rebecca, The trick I normally use is to copy the new column and paste it back as just values. If you right click and paste, you'll get a bunch of options. You'll want one that just pastes values. Then you can get rid of the first two columns.

Are you referring to autofill? I have a tutorial that covers autofill here: www.easyprogramming.net/exceltutorials/autofill_excel.php I show you how to drag any function all the way by dragging or double clicking. Starts around the 9:00 minute mark.

You can set up conditional formatting to get different colors based on variable (assuming that's what you are looking to do). You can see the tutorial here: www.easyprogramming.net/exceltutorials/conditional_formatting.php You can also download the example file on that page so you can go straight to the formula.

First enable "Wrap Text" for that cell, then you can concatentate "CHAR(10)" anywhere you want a line break. For Example to enter a line break between A1 and B1, you'd do: =A1 & CHAR(10) &B1 This only works if you have "Wrap Text" enabled for that cell.

Nazmus Nasir Thanks for the reply! That worked for what I needed. 👍🏽

Nazmus Nasir One more question. The number that I type in needs to have decimal places. And when it goes through the function and adds in a letter (z-3.0) it looses the .0 and just reads (z-3) the increase decimal button isn’t working because of the letter z in the box. I’m using this to generate code for a Cnc machine and it needs to read z-3.0

@@trace12345678900You can convert the the 3.0 to a text within the formula and add the .0 like this: =A2&"-"&TEXT(B2,"#.0") Assuming B2 is where your 3.0 is.

if you keep the reference and delete the column, then the cell will break. If you want to keep that data and remove the reference column, you should copy your newly concatenated cell and paste it right onto it self as value. If you right-click, under paste, you should see a few options that say "paste as value" - once you do that, the formula will be gone and you can delete the reference columns.

You can use the concat formula like this: =concat(a1:z1) and it'll concatenate that whole row. But you won't be able to add spaces there unless you have cells in your range with spaces.

When you are autofilling, instead of dragging, double click on the little square dot at the bottom right. It should fill all the way down. I demo around minute 9:00 of the auto fill tutorial here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4_t9EMXACZQ.html

Can you clarify a bit more? Do you want to format the cell to be a number? Concatenations mostly work with strings.

You just want to copy two cells from sheet 1 into sheet 2? It'll work the same way, in sheet 2, start with the equal sign to start the formula, head to sheet1 and select your cells. It'll end up looking something like this: =Sheet1!A1&" "&Sheet1!A50

You may be able to do that with VB more easily. With just basic excel formulas, you'd have to combine a whole bunch formulas to do so. The easiest solution may be to split your comma-separated values into different cells and then concatenate them in a 4th cell. It would give you more control and probably look neater. Otherwise I found this article that may help you: exceljet.net/formula/split-text-with-delimiter That shows you how to split values with a delimiter, you can use it in combination with this concatenation tutorial to get the result you're looking for.

If you want to add a line break, you need to do two things. The first is in your formula, add "CHAR(10)" where ever you want the line break. For example if if "JOHN" is in Cell A1 and "Smith" is in B1: = A1 & CHAR(10) & B1 The CHAR(10) will add the line break character in the formula. Second thing you need to do is select the cell or column, and in the 'Home' tab, turn on 'Wrap Text" - it should be in the alignment section. It'll tell the formula to do a line break on Char(10). Hope this helps!

I just tested this and I was able to concatenate. What does your data look like? Is it just text in one column and number in another? Or do you have formulas?

I haven't used MS Access is maybe 8 years. I know there's a concat method you can use when you query. Check this out here: www.techonthenet.com/access/functions/string/concat.php

If you're copying from word and they're being listed in one column, you can copy that column again and then paste as transpose. When pasting, if you right-click, the 4th paste option should say "Transpose" - this will take your rows and paste them as different columns. Hope that helps!

yes, it works, but only with 5 names at the time, thanks anyway,

That's very weird. I just tested with 25 items, works without any issues. What version of Office are you running? And what OS?

excell 15.13.3 copyright 2015, os el capitan 10.11.6

I don't have a Mac to test on. So I'm wondering if that's a mac only issue. I tried a couple other times, haven't been able to replicate it on my windows machine.

Are you talking about just adding numbers of concatenating them? Can you provide an example?

If you right click on the cell, and paste values only, it'll paste the values instead of the formula.

After you drag it down and it copies the first line, can you click into the second cell and look at the formula and share it here. I wonder if for some reason it's using an absolute reference.

Can you be a little more specific? Do you mean if it will concatenate numbers?

You can use the "left" formula for that. So your formula would look something like this: =LEFT(A1,2) & LEFT(B1,3) That games the first 2 characters of A1 and first 3 of B1. You can see the tutorial on the len, left, mid, and right formulas here: www.easyprogramming.net/exceltutorials/len_right_left_mid.php You can also get the example file from that. Good luck!

Something like this should work: =A1&":"&B1 You can add your own text inside the quotes. I put a space in the video but you can replace it with a colon.

@@naztronaut thanks

Are you trying to get both numbers? What does your formula look like? Vlookup will normally get the first value that it matches so it makes sense why you are seeing just one.

Also realized that this isn't my vlookup tutorial, check that out here: www.easyprogramming.net/exceltutorials/vlookup_function.php

Yes but if you use this method, it'll turn the numbers into text values and you won't be able to do calculations. If that's not a concern, then it will work exactly the same.

Hey there, You can make macros, but unfortunately I haven't had any videos on that :(

Just add the following: &", "& It's similar to adding a space that starts at 3:45 in the video.

Right! Have been looking for this comment.

Please start your own channel.

same :(

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-km2CpKkP4xU.html Already found the answer, thanks tho! :)

@@MrKristoSoitu Awesome! Yup, paste as values is the best way course of action.

If i select 1 cell of column C I get formula instead of actual value "John Smith". How can I get only "John Smith" ???

If you right click, you should have paste options where you can select to paste the formula or data. If you hover over the options, it'll tell you what it'll paste.

Hey there, I just tested it and it still works. What result are you seeing when you try? You can also try the concatenate function, your example above would translate to this: =CONCATENATE(C6,"-",G6) Good luck!

I figured it out, tried it on a new document and it works. Something with the number format from where I copied the data was messing it up.

@@FenixDown147 Ah glad you figured it out!

Are you going to make a new cell or are you trying to merge A1 into B1? If the latter, then you can't do it without overriding one of the values. If you're making a new Cell and you're in the same column (let's say A1 and A2) then just do =A1&A2 into your new cell. Then you can copy that into whichever cell you'd like. Hope this helps!

Sir 1 thing more i want to ask i hope u'll help me...i have emails and names in 1 column example:A1=JOHN A2=EMAIL, A3=ALI AND A4= EMAIL and countinous...means in same coulum firstly name and then their e-mail sir how i copy their names and paste in front of email...?they are thousand male its hard to copy one by one

@@attiqurrehman6134 That's a tough one. There could be a few ways to do it and one that comes to the top of my head is doing a formula like this: =A1&A2 Then you autofill all the way down. This will make all Even numbered rows wrong. You can then select all Even Number Rows and remove them. You can also set up a filter by creating a third column with this formula: =ISEVEN(ROW(A1)) That means if the row is an "odd" number like A1, it'll be "FALSE" otherwise it'll be 'TRUE' - you can then select all TRUE values and filter them out. Let me know if this helps in any way. It's a couple more steps but I think this would be the easiest path for you.

@@naztronaut hye, thanks for information...sir, if i send u a screenshot then u can understand better and there'll be more beneficial for me if u make a video on it....sir if u done this than i'll be very thankful for your help & sir u can save my time...its request

@@attiqurrehman6134 I wish I had time to make these videos. Sadly I don't think I'll be making any more Excel videos anytime soon and no videos until early 2020 at this point.

What does your formula look like and which cells is the data in?

I have it as =A2&”-“&B2”-“&C2 & it doesn’t merge the info together it’ll just stay in the format or it’ll give me an error message. 😖

Okay now it’s not letting me grab C2.

@@amigarza8090 You're missing an ampersand after the B2. It should be this: =A2&"-"&B2&"-"&C2

OMG THANK YOU! 🤣 I’ve been trying to figure it out since 9 this morning! Thank you so much !

When I copy and paste your formula, your quotes are 2 single quotes so it fails, Can you switch it to double quotes instead? You can copy and paste this: =D327&" "&E327

@@naztronaut what do u mean by 2 Single quotes ?

@@Akhan1999 I meant the apostrophes like this: ' I'm too used to programming speak. Apostrophes are called single quotes and regular quotation marks are double quotes.

I know bro. I do have a pinned comment explaining why. I used to teach Excel in a classroom and found it helpful to talk about the "why" more than the "how" so I unintentionally transferred that into my older videos. Newer videos are more on point but I still try to focus on the why because I think it's important.

Where did you get stuck?

Thanks for the feedback. I'll admit my older videos weren't as organized but I try to focus on the 'why' as well as the 'how' so my videos tend to be more wordy. I also use a lot of analogies because the videos are meant to be for beginners and all those words add up. Thanks for listening!

I'll admit my older videos weren't as organized as I tried to focus on the 'why' as well as the 'how' so my videos tend to be more wordy. I also use a lot of analogies because the videos are meant to be for beginners and all those words add up. Thanks for listening!

Thanks for the feedback. I'll admit my older videos weren't as organized but I try to focus on the 'why' as well as the 'how' so my videos tend to be more wordy. I also use a lot of analogies because the videos are meant to be for beginners and all those words add up. Thanks for watching!

Thanks for the feedback. I'll admit my older videos weren't as organized but I try to focus on the 'why' as well as the 'how' so my videos tend to be more wordy. I also use a lot of analogies because the videos are meant to be for beginners and all those words add up. Sorry for the lengthy video! Thanks for watching!

@@naztronaut Thank you too for your time to do this for all of us to improve.

Thanks for the feedback. I'll admit my older videos weren't as organized but I try to focus on the 'why' as well as the 'how' so my videos tend to be more wordy. I also use a lot of analogies because the videos are meant to be for beginners. Thanks for listening!