Need short Macro for formatting task

[TeachMeToExcel]

THANK YOU SO MUCH FOR ANY TIME AND EFFORT IN HELPING ME SAVE MANY MAN HOURS AT WORK!!!!

I just need to take a large data set from the first format below to the second. The changes include:
1. New column
2. Each row from the original data set that begins with cell A containing "RE: $ (number)", that cell and the proceeding B cell are moved to new column as pictured
3. Row from which they were moved is deleted
4. Trim function for all data in the set, since original format often includes spaces before data.

Before Formatting:

RE: $ 157,925,000	PENNSYLVANIA HOUSING FINANCE AGENCY
Belle Haven Investments	10/1/2032​	1.65​	5​	90​	450​	70879QKR3
Blackrock Financial Management	4/1/2022​	5​	2.5​	50​	125​	70879QKY8
Boyd Watterson Asset Management	4/1/2023​	5​	2.5​	10​	25​	70879QLA9
Citizens Bank	10/1/2027​	0.8​	5​	3.75​	18.75​	70879QKF9
Veritable Investment Consultants, LP	4/1/2029​	1.1​	5​	10​	50​	70879QKJ1
RE: $ 43,410,000	MONTANA BOARD OF HOUSING
Capital Research	6/1/2051​	3​	5​	1,410​	7,050.00​	61212WMU6
First and Peoples Bank & Trust	12/1/2041​	2​	6.25​	2​	12.5​	61212WMR3
Kemper Corporation	12/1/2050​	2.125​	6.25​	58.5​	365.63​	61212WMT9

After formatting:

RE: $ 157,925,000	Blackrock Financial Management	4/1/2022​	5​	2.5​	50​	125​	70879QKY8
PENNSYLVANIA HOUSING FINANCE AGENCY	Boyd Watterson Asset Management	4/1/2023​	5​	2.5​	10​	25​	70879QLA9
	Citizens Bank	10/1/2027​	0.8​	5​	3.75​	18.75​	70879QKF9
	Veritable Investment Consultants, LP	4/1/2029​	1.1​	5​	10​	50​	70879QKJ1
RE: $ 43,410,000	Capital Research	6/1/2051​	3​	5​	1,410​	7,050.00​	61212WMU6
MONTANA BOARD OF HOUSING	First and Peoples Bank & Trust	12/1/2041​	2​	6.25​	2​	12.5​	61212WMR3
	Kemper Corporation	12/1/2050​	2.125​	6.25​	58.5​	365.63​	61212WMT9

 

[DanteAmor]

Try this:

Your data in Sheet1 starting in cell A1. The results on Sheet2.

Sub formatting_task()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
  j = 1
  For i = 1 To UBound(a, 1)
    If UCase(Left(a(i, 1), 3)) = "RE:" Then
      b(j, 1) = WorksheetFunction.Trim(a(i, 1))
      b(j + 1, 1) = WorksheetFunction.Trim(a(i, 2))
    Else
      For k = 1 To UBound(a, 2)
        b(j, k + 1) = WorksheetFunction.Trim(a(i, k))
      Next
      j = j + 1
    End If
  Next
  Sheets("Sheet2").Range("A1").Resize(j, UBound(b, 2)).Value = b
End Sub

 

[TeachMeToExcel]

Try this:

Your data in Sheet1 starting in cell A1. The results on Sheet2.

Sub formatting_task()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
  j = 1
  For i = 1 To UBound(a, 1)
    If UCase(Left(a(i, 1), 3)) = "RE:" Then
      b(j, 1) = WorksheetFunction.Trim(a(i, 1))
      b(j + 1, 1) = WorksheetFunction.Trim(a(i, 2))
    Else
      For k = 1 To UBound(a, 2)
        b(j, k + 1) = WorksheetFunction.Trim(a(i, k))
      Next
      j = j + 1
    End If
  Next
  Sheets("Sheet2").Range("A1").Resize(j, UBound(b, 2)).Value = b
End Sub

This added a new instance in Sheet2, trimmed the data, created a new column, but did not move the "RE: $ 157,925,000" and the "PENNSYLVANIA HOUSING FINANCE AGENCY" into the new column/rows or delete the row from which they came. Thanks!

 

[DanteAmor]

2. Each row from the original data set that begins with cell A containing "RE: $ (number)"

Maybe you don't have "RE:" as you mentioned at the beginning.
With this data on sheet1.

Dante Amor
	A	B	C	D	E	F	G
1	RE: $ 157,925,000	PENNSYLVANIA HOUSING FINANCE AGENCY
2	Belle Haven Investments	10/01/2032	1.65	5	90	450	70879QKR3
3	Blackrock Financial Management	04/01/2022	5	2.5	50	125	70879QKY8
4	Boyd Watterson Asset Management	04/01/2023	5	2.5	10	25	70879QLA9
5	Citizens Bank	10/01/2027	0.8	5	3.75	18.75	70879QKF9
6	Veritable Investment Consultants, LP	04/01/2029	1.1	5	10	50	70879QKJ1
7	RE: $ 43,410,000	MONTANA BOARD OF HOUSING
8	Capital Research	06/01/2051	3	5	1,410	7,050.00	61212WMU6
9	First and Peoples Bank & Trust	12/01/2041	2	6.3	2	12.5	61212WMR3
10	Kemper Corporation	12/01/2050	2.13	6.3	58.5	365.63	61212WMT9
Sheet1

The result is on sheet2.

Dante Amor
	A	B	C	D	E	F	G	H
1	RE: $ 157,925,000	Belle Haven Investments	01/10/2032	1.65	5	90	450	70879QKR3
2	PENNSYLVANIA HOUSING FINANCE AGENCY	Blackrock Financial Management	01/04/2022	5	2.5	50	125	70879QKY8
3		Boyd Watterson Asset Management	01/04/2023	5	2.5	10	25	70879QLA9
4		Citizens Bank	01/10/2027	0.8	5	3.75	18.75	70879QKF9
5		Veritable Investment Consultants, LP	01/04/2029	1.1	5	10	50	70879QKJ1
6	RE: $ 43,410,000	Capital Research	01/06/2051	3	5	1410	7050	61212WMU6
7	MONTANA BOARD OF HOUSING	First and Peoples Bank & Trust	01/12/2041	2	6.25	2	12.5	61212WMR3
8		Kemper Corporation	01/12/2050	2.125	6.25	58.5	365.63	61212WMT9
Sheet2

Try again with the following macro. If you have problems, then you could put the data with which you are testing here, but use the XL2BB tool minisheet, to have a sample closer to the reality of your data.

Sub formatting_task()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, lr As Long, lc As Long
  Dim dato As String
  
  With Sheets("Sheet1")
    lr = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    lc = .UsedRange.Columns(.UsedRange.Columns.Count).Column
    a = .Range("A1", .Cells(lr, lc)).Value
  End With
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
  j = 1
  For i = 1 To UBound(a, 1)
    dato = UCase(Replace(a(i, 1), " ", ""))
    If InStr(1, dato, "RE:") > 0 Then
      b(j, 1) = WorksheetFunction.Trim(a(i, 1))
      b(j + 1, 1) = WorksheetFunction.Trim(a(i, 2))
    Else
      For k = 1 To UBound(a, 2)
        b(j, k + 1) = WorksheetFunction.Trim(a(i, k))
      Next
      j = j + 1
    End If
  Next
  Sheets("Sheet2").Range("A1").Resize(j, UBound(b, 2)).Value = b
End Sub