Combine multiple cells

pllooi

New Member
Joined
Jun 23, 2019
Messages
2
Hi everyone, need help to cover excel formula =(B2&"_"&G2&"_"&I2) to vba code. Because I get Alert msg for excel "One or more formulas in this workbook are longer than the allowed limit of 8192 character."

I need to combine B$&G$&I$ in-between space insert "_" to F$ (automate fill up when key in data on Columns B,G & I

[TABLE="width: 1060"]
<colgroup><col width="87" style="width: 65pt;"><col width="99" style="width: 74pt;"><col width="81" style="width: 61pt;"><col width="85" span="2" style="width: 64pt;"><col width="299" style="width: 224pt;"><col width="191" style="width: 143pt;"><col width="73" style="width: 55pt;"><col width="60" style="width: 45pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]TX Date[/TD]
[TD="class: xl64, width: 99, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]ID[/TD]
[TD="class: xl64, width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]Source[/TD]
[TD="class: xl63, width: 85, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]Ingested by[/TD]
[TD="class: xl63, width: 85, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]Ingest Date[/TD]
[TD="class: xl64, width: 299, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]Filename[/TD]
[TD="class: xl64, width: 191, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]Title[/TD]
[TD="class: xl64, width: 73, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]节目名称[/TD]
[TD="class: xl65, width: 60, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8EA9DB]#8EA9DB[/URL] , align: center"]Episode[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]43475[/TD]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]AATZX01EX[/TD]
[TD="class: xl67, align: center"][/TD]
[TD="class: xl68, align: center"][/TD]
[TD="class: xl68, align: center"][/TD]
[TD="class: xl70, bgcolor: yellow"]AATZX01EX_The World Hunter S1_TR[/TD]
[TD="class: xl72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]The World Hunter S1[/TD]
[TD="class: xl73, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"] [/TD]
[TD="class: xl74, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]TR[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]43475[/TD]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]AATZY01EX[/TD]
[TD="class: xl67, align: center"][/TD]
[TD="class: xl68, align: center"][/TD]
[TD="class: xl68, align: center"][/TD]
[TD="class: xl70, bgcolor: yellow"]AATZY01EX_The World Hunter S1_S[/TD]
[TD="class: xl72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]The World Hunter S1[/TD]
[TD="class: xl73, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"] [/TD]
[TD="class: xl74, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]S[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]43475[/TD]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]AATZZ01EX[/TD]
[TD="class: xl66, align: center"]Aspera[/TD]
[TD="class: xl69, align: center"]Yiwen[/TD]
[TD="class: xl69, align: center"]25 Jan 18[/TD]
[TD="class: xl70, bgcolor: yellow"]AATZZ01EX_The World Hunter S1_1[/TD]
[TD="class: xl72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]The World Hunter S1[/TD]
[TD="class: xl73, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"] [/TD]
[TD="class: xl74, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]43475[/TD]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]AAUAA01EX[/TD]
[TD="class: xl66, align: center"]Aspera[/TD]
[TD="class: xl69, align: center"]Grace Au[/TD]
[TD="class: xl69, align: center"]26 Feb 18[/TD]
[TD="class: xl70, bgcolor: yellow"]AAUAA01EX_The World Hunter S1_2[/TD]
[TD="class: xl72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]The World Hunter S1[/TD]
[TD="class: xl73, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"] [/TD]
[TD="class: xl74, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Regards,
LOOI
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Like this?
Code:
Sub CONCAT()
Application.ScreenUpdating = False


Dim Last_Row As Long, X As Long


Last_Row = ActiveSheet.UsedRange.Rows.Count


For X = 2 To Last_Row


ActiveSheet.Range("F" & X).Value2 = ActiveSheet.Range("B" & X).Value2 & "_" & ActiveSheet.Range("G" & X).Value2 & "_" & ActiveSheet.Range("I" & X).Value2


Next X


Application.ScreenUpdating = True


End Sub
 
Upvote 0
Thank you.

Like this?
Code:
Sub CONCAT()
Application.ScreenUpdating = False


Dim Last_Row As Long, X As Long


Last_Row = ActiveSheet.UsedRange.Rows.Count


For X = 2 To Last_Row


ActiveSheet.Range("F" & X).Value2 = ActiveSheet.Range("B" & X).Value2 & "_" & ActiveSheet.Range("G" & X).Value2 & "_" & ActiveSheet.Range("I" & X).Value2


Next X


Application.ScreenUpdating = True


End Sub
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top