VBA for Excel Spreadsheet

You could put a formula on a second column like this:

=IF(LEFT(A1,1)="A",MID(A1, 2, 5),"")

This will extract the information that you want. Then you could Autofilter this column to list all excepet blanks and copy the result to a new sheet.

jppinto

I actually need VBA for this. It's for an end user and I would like for them to be able to just press a button. (run a macro)

That won't work either. There will be a different number of records in my file each month. What I need is VBA that will go through the whole column and if it begins with an A, strip it off, then do the mid function.

If you highlight the whole column before performing the find function, that should help, that way it will only look in that column

please provide an excel file

Here's what the VBA looks like after recording the macro. In the 2nd to last and last line where it says 'Range' can I just change that to B2:B2 and that will take care of the whole column no matter how many records?
Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[1],1)=""A"", MID(RC[1], 2, 5), RC[1])"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B2204")
    Range("B2:B2204").Select
End Sub

Not B2:B2, but you could do
B2:B(choose a number that you think would be the maximum number of rows like 60,000)

There should be some function, I'm not a VBA developer (obviously) but I think it's transpose or array or count? Something that will count the actual number of records and update only that range. I really don't know how many records my file will be and don't like putting in a random number to account for the range.

Sorry for the delay, I just got back from vacation. I will test this either later on today or early tomorrow.

Thanks.