Need help with a macro

[JGNWA]

Need help with a macro to evaluate each row of text. If text starts with any date and time to delete both the date and time and leave remaining text. If row text begins with "AC- " delete "AC- " from the text and leave the remaining text.
Thanks for the help.

LOAN PAYMENT TO CL-0000000241011050

12/27 20:40 Etsy.com - AnywayS Brooklyn NY CKCD DEBIT 8040

12/29 16:18 LOWE'S POS DEBIT 8040

AC-CAPITAL ONE -CRCARDPMT

12/30 08:13 APPLE.COM/BILL 866-712-7753 CA CKCD DEBIT 8040

12/30 12:43 HARP'S FOOD ST POS DEBIT 6507

12/30 12:58 WALGREENS 4015 POS DEBIT 6507

AC-APPLECARD GSBANK-PAYMENT

01/01 02:04 discovery+ 0123456789 TN CKCD DEBIT 6507

01/01 21:03 APPLE.COM/BILL 866-712-7753 CA CKCD DEBIT 8040

01/02 09:46 APPLE.COM/BILL 866-712-7753 CA CKCD DEBIT 8040

01/03 12:05 COLLIER DRUG FAR POS DEBIT 6507

01/03 12:21 WAL-MART Wal-Mar POS DEBIT 6507

12/30 02:01 MCDONALD EYE A CKCD DEBIT 8040

12/31 09:04 PANDA EXPRESSR CKCD DEBIT 6507

12/31 21:36 MCALISTER'S CKCD DEBIT 6507

AC-BENEFIT PAYMENTS-DEPOSIT

AC-ELECTRIC -PPDDRAFT

AC- ELECTRIC -PPDDRAFT

AC-CITY UTILITYPMT

 

[offthelip]

Welcome to the forum, You haven't specified where the column of data is, so I assumed in range A1 to A20, but just change the reference to A1:A20 in the two places in the code to your needs:

Sub test2()
inarr = Range("a1:A20")
For i = 1 To UBound(inarr, 1)
If Mid(inarr(i, 1), 3, 1) = "/" And Mid(inarr(i, 1), 9, 1) = ":" Then
inarr(i, 1) = Mid(inarr(i, 1), 13)
Else
 If Left(inarr(i, 1), 3) = "AC-" Then
  inarr(i, 1) = Mid(inarr(i, 1), 4)
 End If
End If
Next i

Range("a1:A20") = inarr
End Sub

 

[Armstrong_N14]

maybe this will not help at all and I know less when it comes to Macro.
Have you tried using SUBSTITUTE and RIGHT or LEFT formulas?

SUBSTITUTE function - Microsoft Support

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

support.microsoft.com

LEFT, LEFTB functions - Microsoft Support

LEFT returns the first character or characters in a text string, based on the number of characters you specify. LEFTB returns the first character or characters in a text string, based on the number of bytes you specify.

support.microsoft.com

good luck

 

[JGNWA]

Welcome to the forum, You haven't specified where the column of data is, so I assumed in range A1 to A20, but just change the reference to A1:A20 in the two places in the code to your needs:

Sub test2()
inarr = Range("a1:A20")
For i = 1 To UBound(inarr, 1)
If Mid(inarr(i, 1), 3, 1) = "/" And Mid(inarr(i, 1), 9, 1) = ":" Then
inarr(i, 1) = Mid(inarr(i, 1), 13)
Else
 If Left(inarr(i, 1), 3) = "AC-" Then
  inarr(i, 1) = Mid(inarr(i, 1), 4)
 End If
End If
Next i

Range("a1:A20") = inarr
End Sub

Thanks so much!!!