Macro to cut first 20 characters and paste, delete characteres 21 to 23 and delete last 4 characters and keep the rest

[PRVANWYK]

Good day all,
I'm in a desperate need for macro to solve the following:
MP200-000-60-200-000 - HOPPER AERATION AIR MANIFOLD UNIT 1-6.iam

I need the first 20 characters to be cut and pasted in another cell, then the "space-dash-space" to be deleted, the title part must be kept and the .iam must be deleted.
I have about 25000 entries acros various spreadsheets.
I used to have a macro to delete the last 4 characters from the cell.
But now I need a bit more.
All the data is in Column A. I know I can use the MID function to a certain degree but I can't have a linked cell in my results as I need to use it again in another application that will pick up the formula.

Please help.
Thank you

 

[wigi]

For instance:

Sub f()
    For Each r In Columns(1).SpecialCells(2, 2)
        r.Offset(, 1) = Split(r, " - ")(0)
        r.Value = Left(Split(r, " - ")(1), Len(Split(r, " - ")(1)) - 4)
    Next
End Sub

or:

Sub f()
    For Each r In Columns(1).SpecialCells(2, 2)
        sq = Split(r, " - ")
        r.Offset(, 1) = sq(0)
        r.Value = Left(sq(1), Len(sq(1)) - 4)
    Next
End Sub

 

[PRVANWYK]

Absolutely awesome!!
Many many thanks!!
Works like a charm

 

[jim may]

Can you use this?

Excel Workbook
	A	B	C
1	MP200-000-60-200-000 - HOPPER AERATION AIR MANIFOLD UNIT 1-6.iam	MP200-000-60-200-000	HOPPER AERATION AIR MANIFOLD UNIT 1-6
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=LEFT(A1,20)
C1	=MID(A1,LEN(B1)+4,(LEN(MID(A1,LEN(B1)+4,256))-4))

 

[PRVANWYK]

Hi Jim,
I need to move data without the formulas to another application, but thank you.
I do appreciate the reply.
regards

 

[jim may]

You can of course copy both columns B & C and paste them AS VALUES, in place or to Columns D & E. Oh well... FWIW

 

[PRVANWYK]

Ah that will also work,
Thank you very much.
I'll give that a go now.
Awesome