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

[mtbthepro]

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

      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

        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

 

[Legacy 396030]

Please show a screen shot or specify the exact data in each column. I believe they can be combined with formulas.

 

[AkaTrouble]

if i understand this can be done with a formula your example in A1

|Account#:12542-|5651256|"

<colgroup><col></colgroup><tbody>
</tbody>

in B1

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

returns

|Account#:12542-5651256|"

<colgroup><col></colgroup><tbody>
</tbody>

 

[AkaTrouble]

if you want to do it in VBA with a macro

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

 

[mtbthepro]

Department 14 Youth Sports
MEMBER NUMBER	MEMBER NAME	TRN DATE	0 -30 DAYS	31-60 DAYS	61-90 DAYS	OVER 90 DAYS	PAST DUE
ACCOUNT#: 1-026-1	4-00000-3345
026-000180284-02	example, example	1/21/2017	15.62	0	0	0	15.62
	ACC	OUNT TOT	15.62	0	0	0	15.62
Department 52 Phys ,(Excluding Aquatics
MEMBER NUMBER	MEMBER NAME	TRN DATE	0 -30 DAYS	31-60 DAYS	61-90 DAYS	OVER 90 DAYS	PAST DUE
ACCOUNT#: 1-026-5	2-00000-3346
020-000066765-01	example, example	1/6/2017	123	0	0	0	123
	ACC	OUNT TOT	123	0	0	0	123

<tbody>
</tbody>

 

[AkaTrouble]

ok ignore my offerings i totally misunderstood .. sorry i will leave this for others to ponder

 

[mtbthepro]

Department 14 Youth Sports
MEMBER NUMBER	MEMBER NAME	TRN DATE	0 -30 DAYS	31-60 DAYS	61-90 DAYS	OVER 90 DAYS	PAST DUE
ACCOUNT#: 1-026-1	4-00000-3345
026-000180284-02	example, example	1/21/2017	15.62	0	0	0	15.62
	ACC	OUNT TOT	15.62	0	0	0	15.62
Department 52 Phys ,(Excluding Aquatics
MEMBER NUMBER	MEMBER NAME	TRN DATE	0 -30 DAYS	31-60 DAYS	61-90 DAYS	OVER 90 DAYS	PAST DUE
ACCOUNT#: 1-026-5	2-00000-3346
020-000066765-01	example, example	1/6/2017	123	0	0	0	123
	ACC	OUNT TOT	123	0	0	0	123

<tbody>
</tbody>

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

ACCOUNT#: 1-026-52-00000-3346

<tbody>
</tbody>

 

[Legacy 396030]

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.

 

[Rick Rothstein]

See if this macro does what you want...

[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]

 

[mtbthepro]

Actually

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