Extract Last Number of Bracketed Filename

[BadDogTitan]

[h=3][/h]

I have thousands of filenames listed in a worksheet in the following format:
Filename 1 (A101 8.2 S3)
Filename 2 (A5 9 R14B)
Filename 3 (A45 27 M24)
How would I use VBA to extract the last number and place in the adjacent cell (as below)?
Filename 1 S3
Filename 2 R14B
Filename 3 M24
​

Thanks!

 

[tyija1995]

Assuming that your Filename 1 (A101 8.2 S3) appears in cell A2 try this in cell B2:

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))),")","")

If you want to preserve the first two words then use this formula:

TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2)))&" "&SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))),")","")

In the example of "Filename 1 (A101 8.2 S3)" this will return "Filename 1 S3"

 

[BadDogTitan]

Is that a worksheet function? Is there a VBA equivalent?

 

[tyija1995]

This is just a standard Excel function, I can look at converting to VBA though

 

[BadDogTitan]

That would be fantastic!

 

[tyija1995]

This procedure places the formula in the cell 1 column across from the data, so if your data is in cells A2:A100 select cells B2:B100 and run the procedure.

Sub formula()
    Dim rng As Range
    Set rng = Selection
    Selection.FormulaR1C1 = _
        "=TRIM(LEFT(SUBSTITUTE(R[-0]C[-1],"" "",REPT("" "",LEN(R[-0]C[-1]))),2*LEN(R[-0]C[-1])))&"" ""&SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(R[-0]C[-1],"" "",REPT("" "",LEN(R[-0]C[-1]))),LEN(R[-0]C[-1]))),"")"","""")"
End Sub

 

[tyija1995]

Or if you want a function then use this:

Function ExtractFormula(txt As String) As String
    ExtractFormula = Application.Trim(Left(WorksheetFunction.Substitute(txt, " ", WorksheetFunction.Rept(" ", Len(txt))), 2 * Len(txt))) & _
    " " & WorksheetFunction.Substitute(Trim(Right(WorksheetFunction.Substitute(txt, " ", WorksheetFunction.Rept(" ", Len(txt))), Len(txt))), ")", "")
End Function

to use:
=ExtractFormula(A2)
Where A2 is your file name - it will return the first two words (words are defined by spaces) and the last word