Combine multiple cells

P

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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
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.

MoshiM said:
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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
macro to Insert one named range at numerous changing rows containing the macro button inserting relative to the position of macro button used
Replies
0
Views
986
jtatt
J
R
  • Question Question
Visualizing relationship of similar tasks across many different Projects
Replies
1
Views
624
RogerC
R
J
  • Question Question
Multiple lookups to return a value
Replies
3
Views
451
AlanY
A
M
  • Question Question
Dynamic List Of Unique Items based on Multiple Criteria
Replies
4
Views
871
Aladin Akyurek
Aladin Akyurek
M
  • Question Question
Conditional combination's pick only "1" number from each column in the row
2
Replies
14
Views
2K
motilulla
M

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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