VBA: How to combine string from two different cells into one?

M

mtbthepro

Board Regular
Joined
Feb 22, 2017
Messages
91
Hi there,
I would like to know if there is a way to combine two strings together into one cell. I am talking about putting numbers together, there is a set of Account#which are on different lines and they are displayed like |Account#:12542-|5651256|" The pipe (|) is there to show the column line that runs in between them. I would like that line to disappear and the number after the "-" to be in the same cell as "Account#: " so end result should look something like "|Account#:12542-5651256|"
I know there is a way to do it which is called concatenate and I have tried it a couple of different ways but it doesn't work.
If you can help me with this I would greatly appreciate it.




example one
Code: 
      For N = nlast To 1 Step -1
                        If ws.Cells(N, 1).Value Like "*ACCOUNT*" Then
                            ws.Cells(N, 1).Value = str1
                            ws.Rows(N).EntireRow.Font.Bold = True
                            ws.Rows(N).Offset(0, 1) = str2
                            ws.Rows(N) = CONCATENATE(str1 & str2)  
                        End If
                    Next N
example two
Code: 
        st= .Cells(.Rows.Count, "A")
                    For i = i To 1 Step -1
                If (ws.Cells(i, 1).Value Like "*ACCOUNT*") Then
                    st1= RTrim(.Cells(i, "A") & .Cells(i, "B"))
                        End If
                    Next i
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Please show a screen shot or specify the exact data in each column. I believe they can be combined with formulas.
 
Upvote 0
if i understand this can be done with a formula your example in A1
[TABLE="width: 308"]
<colgroup><col></colgroup><tbody>[TR]
[TD]|Account#:12542-|5651256|" [/TD]
[/TR]
</tbody>[/TABLE]

in B1

Code: 
=SUBSTITUTE(A1,"-|","-",1)

returns

[TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD]|Account#:12542-5651256|" [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
if you want to do it in VBA with a macro

Code: 
Option Explicit
Sub RemoveDelimit()

Dim c As Range
        For Each c In ActiveSheet.UsedRange.Columns("A").Cells
            c.Value = WorksheetFunction.Substitute(c.Value, "-|", "-", 1)

        Next c

End Sub
 
Upvote 0
[TABLE="width: 820"]
<tbody>[TR]
[TD="colspan: 3"]Department 14 Youth Sports[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MEMBER NUMBER[/TD]
[TD]MEMBER NAME[/TD]
[TD]TRN DATE[/TD]
[TD]0 -30 DAYS[/TD]
[TD]31-60 DAYS[/TD]
[TD]61-90 DAYS[/TD]
[TD]OVER 90 DAYS[/TD]
[TD]PAST DUE[/TD]
[/TR]
[TR]
[TD]ACCOUNT#: 1-026-1[/TD]
[TD]4-00000-3345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]026-000180284-02[/TD]
[TD]example, example[/TD]
[TD="align: right"]1/21/2017[/TD]
[TD="align: right"]15.62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15.62[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ACC[/TD]
[TD]OUNT TOT[/TD]
[TD="align: right"]15.62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15.62[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Department 52 Phys ,(Excluding Aquatics[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MEMBER NUMBER[/TD]
[TD]MEMBER NAME[/TD]
[TD]TRN DATE[/TD]
[TD]0 -30 DAYS[/TD]
[TD]31-60 DAYS[/TD]
[TD]61-90 DAYS[/TD]
[TD]OVER 90 DAYS[/TD]
[TD]PAST DUE[/TD]
[/TR]
[TR]
[TD]ACCOUNT#: 1-026-5[/TD]
[TD]2-00000-3346[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]020-000066765-01[/TD]
[TD]example, example[/TD]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ACC[/TD]
[TD]OUNT TOT[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
ok ignore my offerings i totally misunderstood .. sorry i will leave this for others to ponder
 
Last edited:
Upvote 0
mtbthepro said:
[TABLE="width: 820"]
<tbody>[TR]
[TD="colspan: 3"]Department 14 Youth Sports[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MEMBER NUMBER[/TD]
[TD]MEMBER NAME[/TD]
[TD]TRN DATE[/TD]
[TD]0 -30 DAYS[/TD]
[TD]31-60 DAYS[/TD]
[TD]61-90 DAYS[/TD]
[TD]OVER 90 DAYS[/TD]
[TD]PAST DUE[/TD]
[/TR]
[TR]
[TD]ACCOUNT#: 1-026-1[/TD]
[TD]4-00000-3345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]026-000180284-02[/TD]
[TD]example, example[/TD]
[TD="align: right"]1/21/2017[/TD]
[TD="align: right"]15.62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15.62[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ACC[/TD]
[TD]OUNT TOT[/TD]
[TD="align: right"]15.62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15.62[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Department 52 Phys ,(Excluding Aquatics[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MEMBER NUMBER[/TD]
[TD]MEMBER NAME[/TD]
[TD]TRN DATE[/TD]
[TD]0 -30 DAYS[/TD]
[TD]31-60 DAYS[/TD]
[TD]61-90 DAYS[/TD]
[TD]OVER 90 DAYS[/TD]
[TD]PAST DUE[/TD]
[/TR]
[TR]
[TD]ACCOUNT#: 1-026-5[/TD]
[TD]2-00000-3346[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]020-000066765-01[/TD]
[TD]example, example[/TD]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ACC[/TD]
[TD]OUNT TOT[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
So here Account number is at A3 and then next Account number is at A7. So can appear at any row. I would like account number to be in one cell. Like
[TABLE="width: 820"]
<tbody>[TR]
[TD]ACCOUNT#: 1-026-52-00000-3346[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Okay I got it!

A2: ACCOUNT#: 1-026-5
B2: 2-00000-3346
C2: =CONCATENATE(A2,B2)

Just match the columns with the cells and copy down the range.
 
Upvote 0
See if this macro does what you want...
Code: 
[table="width: 500"]
[tr]
	[td]Sub CombineAccountNumberParts()
  Dim R As Long, Data As Variant
  Data = Range("A1:B" & Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "ACCOUNT[#]:*" Then
      Data(R, 1) = Data(R, 1) & Data(R, 2)
      Data(R, 2) = ""
    End If
  Next
  Range("A1").Resize(UBound(Data), 2) = Data
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
[VBA] - Need to Hide one column and need sum of columns
Replies
0
Views
182
rsolanki
R
C
  • Question Question
Duplicates with different data to combine with VBA
Replies
2
Views
403
chipsworld
C
D
  • Question Question
VBA to Remove Text Characters From Strings
Replies
5
Views
628
Fuji
F
N
  • Solved
How to Merge Multiple VBA scripts as one?
Replies
7
Views
310
StephenCrump
S
A
  • Question Question
VBA Combine macros in one depending on text string
Replies
2
Views
508
mumps
mumps

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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