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

TX DateIDSourceIngested byIngest DateFilenameTitle节目名称Episode
43475AATZX01EXAATZX01EX_The World Hunter S1_TRThe World Hunter S1 TR
43475AATZY01EXAATZY01EX_The World Hunter S1_SThe World Hunter S1 S
43475AATZZ01EXAsperaYiwen25 Jan 18AATZZ01EX_The World Hunter S1_1The World Hunter S1 1
43475AAUAA01EXAsperaGrace Au26 Feb 18AAUAA01EX_The World Hunter S1_2The World Hunter S1 2

<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>
</tbody>

Thanks,
Regards,
LOOI
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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