I'm so proud of myself I have to add this. Just watching this video allowed me to finish my formula. =CONCATENATE(IF($E3="United States","USA: ",IF($E3="Mexico","MEX: ",IF($E3="Canada","CAN: "," "))),IF(COUNTA(C3)=1,CONCATENATE($C3,","," ",$D3),"")) AND IT WORKS GREAT!! Thank you
I got similar results combining CONCATENATE with ISBLANK or ISTEXT. Here are the formulas I used: =IF(ISBLANK(C2),CONCATENATE(B2," ",A2),CONCATENATE(B2," ",C2,"."," ",A2)) =IF(ISTEXT(C2),CONCATENATE(B2," ",C2,"."," ",A2),CONCATENATE(B2," ",A2))
thats not the exactly what im looking for BUT you really help me! =IF(C2>=18;CONCATENATE("Congrats! ";C3;" you can vote this coming election");CONCATENATE("Sorry ";C3;" you can't vote this coming election")) and thats works!!
Thanks! I wasn't sure how to get Concatenate + If to combine. After watching this, I did =IF(A4=A5, CONCATENATE(J4," ",J5), 0) which will compare a cell in two different rows and if those two cells have the same value, then the formula will concatenate other cells from each of the rows into one.
Thoroughly enjoy your tutorials. Is there a way to automatically infill a cell on a prescribed date (=now()) to correspond with my direct debits in an income and expenditure spreadsheet, when my direct debit is the same amount on the same day each month through the year.
Hi Ralph. It appears we share the same profession. Your tutorials are very concise. It also helps that you have a very clear voice. Sometimes my students need a bit more help with some of the concepts you cover in your screencasts. I may direct them your way from time to time.
Thank you for the tutorial! You mentioned multiple ways to concatenate text. I would like to add this one: =IF(C2>0,B2&" "&C2&"."&" "&A2,B2&" "&A2) which translates to "-If C2 is not empty -then give me B2 and a space and C2 and a period and a space and A2 -else give me B2 and a space and A2." Using the ampersand as a join is sometimes quicker and clearer than using the =CONCATENATE() formula.
He auto bolded all the cells at once by highlighting the cells he wanted to be bold and then simply by pressing the bold function/tab in the excel menu
Thank you for this video. I have been in dilemma how to join those without middle initial. Now, the problem is, I want to extract the middle initial from the middle name. Can anyone help me how to solve it? Column A - First Name Column B - Middle Name Column C - Surname
You can also use some logic in your formula. The "Logical_test" argument of the IF function sees "1" as TRUE, and a "0" as FALSE. So, if you put it like this: =IF(COUNTA(C4);CONCATENATE.. ETC..) You can get the same result.
12 years in the future this is the only aprox video to use if with concat function. But the document I have is so scrambled I need to do at least 2 if to get a result, and I can't crack it😢
Great. Clear; telling what you do and showing what you tell on screen; zooming the right places. Art!
THANK YOU! This has been bothering me for months, you just saved me so much time!
Best tutorial on concatenate in Excel. Thanks!!!!!
Ive seen a few vids on the CONCATENATE function and this is so unique....
Thank u for sharing
I love all the comments you have, they are thoughtful
I'm so proud of myself I have to add this. Just watching this video allowed me to finish my formula.
=CONCATENATE(IF($E3="United States","USA: ",IF($E3="Mexico","MEX: ",IF($E3="Canada","CAN: "," "))),IF(COUNTA(C3)=1,CONCATENATE($C3,","," ",$D3),"")) AND IT WORKS GREAT!!
Thank you
I got similar results combining CONCATENATE with ISBLANK or ISTEXT. Here are the formulas I used:
=IF(ISBLANK(C2),CONCATENATE(B2," ",A2),CONCATENATE(B2," ",C2,"."," ",A2))
=IF(ISTEXT(C2),CONCATENATE(B2," ",C2,"."," ",A2),CONCATENATE(B2," ",A2))
GREAT VIDEO ! MANY THANKS FOR CLEAR PRESENTATION ! PLEASE MAKE MORE VIDEOS SO HELPFUL
thats not the exactly what im looking for BUT you really help me!
=IF(C2>=18;CONCATENATE("Congrats! ";C3;" you can vote this coming election");CONCATENATE("Sorry ";C3;" you can't vote this coming election"))
and thats works!!
Thanks! I wasn't sure how to get Concatenate + If to combine. After watching this, I did =IF(A4=A5, CONCATENATE(J4," ",J5), 0) which will compare a cell in two different rows and if those two cells have the same value, then the formula will concatenate other cells from each of the rows into one.
Such an excellent explanation. Thanks
So helpful, thank you!
Thoroughly enjoy your tutorials. Is there a way to automatically infill a cell on a prescribed date (=now()) to correspond with my direct debits in an income and expenditure spreadsheet, when my direct debit is the same amount on the same day each month through the year.
Thanks for the clarification, so good
Hi Ralph. It appears we share the same profession. Your tutorials are very concise. It also helps that you have a very clear voice. Sometimes my students need a bit more help with some of the concepts you cover in your screencasts. I may direct them your way from time to time.
Thank you for the tutorial!
You mentioned multiple ways to concatenate text. I would like to add this one:
=IF(C2>0,B2&" "&C2&"."&" "&A2,B2&" "&A2)
which translates to
"-If C2 is not empty
-then give me B2 and a space and C2 and a period and a space and A2
-else give me B2 and a space and A2."
Using the ampersand as a join is sometimes quicker and clearer than using the =CONCATENATE() formula.
gorflunk
=B2&IF(ISBLANK(C2)," "," "&C2&". ")&A2
Works better. Not so long.
great explanation! thx
Great video
but id like to show u an alternative
intead of using the counta function use IF C1"" (which is blank)
So what if I wanted to concatenate 4 columns and there is a possibilty any of those columns could be blank how would I approach that?
Thank you so much for sharing =)
how if i have more than one blank cell? how to solve this problem?
How to calculate row different values in one cell with condition value should be more than or equal to 1
Thank you, it very good
Very helpful. Thank you.
Beutifully explained,Mr Ralph ,Thanks and Regards
Thanks!
thanks for help
I noticed you auto bold the top row. I wonder if there is an option for this in Excel or something complicated.
He auto bolded all the cells at once by highlighting the cells he wanted to be bold and then simply by pressing the bold function/tab in the excel menu
Thank you for this video. I have been in dilemma how to join those without middle initial.
Now, the problem is, I want to extract the middle initial from the middle name. Can anyone help me how to solve it?
Column A - First Name
Column B - Middle Name
Column C - Surname
You can also use some logic in your formula. The "Logical_test" argument of the IF function sees "1" as TRUE, and a "0" as FALSE. So, if you put it like this: =IF(COUNTA(C4);CONCATENATE.. ETC..) You can get the same result.
hi, do you have sample for concatenate+vlookup ? thnx
very sporting for new lerners
Thank You very useful
How to use concatenate with vlookup?
12 years in the future this is the only aprox video to use if with concat function. But the document I have is so scrambled I need to do at least 2 if to get a result, and I can't crack it😢
Pls, help me out. What is missing here =IFS(COUNTA(D2)=1,CONCATENATE(B2,"-",D2), AND Ifs(COUNTA(D2)=0,CONCATENATE(B2,"-",C2)))
Your video looks fuzzy