Macro for adding text, spacing, and a replace in a text string.

[scapegoat9595]

I am a total beginner and I am at a loss on how to achieve this. My goal would be instantly create a file index based on a file list I import into excel. I would like to take something like the following text in a cell...
“01 Draft Discussion Document - Appendix A.pdf” and have it changed to
“Tab 01__________Draft Discussion Document - Appendix A”.
I cant figure out how to go about this smartly.
Is there way to add (10 spaces) at the position of the first space, add “Tab” at the front, and delete the (.pdf/.PDF) at the end? I would want to apply this to all the cells that contain text in Column A.

* I had to use underscores in my example above, in its place would be 10 spaces.

Thanks very much.

[mehmetcik]

Your formula in column B is:-

="Tab " & LEFT(SUBSTITUTE(A2," ","          ",1),LEN(SUBSTITUTE(A2," ","          ",1))-4)

If you want to use a macro to do this on all of column A then:-

Sub Macro1()

    LR = Selection.SpecialCells(xlCellTypeLastCell).Row
    Range("B2:B" & LR).FormulaR1C1 = _
        "=IF(RC[-1]="""","""",""Tab "" & LEFT(SUBSTITUTE(RC[-1],"" "",""          "",1),LEN(SUBSTITUTE(RC[-1],"" "",""          "",1))-4))"
    Selection.Copy
    Range("B2:B" & LR).Value = Range("B2:B" & LR).Value
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Columns.AutoFit
    Range("A1").Select
End Sub

[scapegoat9595]

Amazing. And so clean. I had something that was halfway working, a combination of individual pieces I knew can work. I would have never thought of an approach like this. VERY much appreciated, and happy holidays!