Separating piece of file names into three different column

[megha]

How do I separate the following file name using the formula?

Apr_27_2014_12_54_43_PM_Area3_West. S._12.xls

I want Area 3 in one column, West. S. in second column and 12 in third column. I know this is doable. I have seen the formula before but can't find that file to copy the formula.

Thank you so much!

[ashleyuk1984]

[Kenneth Hobs]

If you know the position, I made this UDF like the Field() in L123. Obviously, you need to set the 3rd parameter value as "_".

'=Field(A1, 2)
Function Field(cell, pos As Integer, Optional delimit As String = " ")
  Dim a() As String
  a() = Split(cell, delimit)
  Field = a(pos - 1)
End Function

[megha]

Thank you for this. I have a long list. The 2nd and 3rd numbers of the formula is not always same. For example, I can't use same formula for "Apr_20_2014_11_06_43_AM_DSU1_Renn. T._9.xls" I am looking for one formula that i can drag for my long spreadsheet.

[megha]

is this doable without macro?

[ashleyuk1984]

Yes it is doable without a macro, but it's also handy to have the complete details in the first place

[Bob Phillips]

The UDF is better, but

B2:=LEFT(MID(A2,25,99),FIND("_",MID(A2,25,99))-1)

C2:=LEFT(SUBSTITUTE(MID($A2,25,99),B2&"_",""),FIND("_",SUBSTITUTE(MID($A2,2 5,99),B2&"_",""))-1)

D2:=LEFT(SUBSTITUTE(MID($A2,25,99),B2&"_"&C2&"_",""),FIND(".xls",SUBSTITUTE (MID($A2,25,99),B2&"_"&C2&"_",""))-1)

[snb]

in B1: =LEFT(MID(A1,25,LEN(A1)),FIND("_",A1,25)-25)
in C1: =MID(A1,26+LEN(B1),SEARCH("_",A1,26+LEN(B1))-26-LEN(B1))
in D1: =LEFT(RIGHT(A4,LEN(A4)-LEN(C6&C8)-26),FIND(".",A4,27+LEN(C6&C8))-27-LEN(C6&C8))

[Kenneth Hobs]

All of the 4 methods posted work for the first string except for mine. Mine needed an extra function to parse out the number part for the last field.

Ashely's method fails for the 2nd string because you did not use a consistent file naming convention.

My numberpart UDF is:

Function NumberPart(aString As String) As Long
  Dim s As String, i As Integer, mc As String
  For i = 1 To Len(aString)
    mc = Mid(aString, i, 1)
    If Asc(mc) >= 48 And Asc(mc) <= 57 Then s = s & mc
  Next i
  NumberPart = CLng(s)
End Function

That UDF works in this case but might not be optimal for other scenarios.

I have attached a test file link showing the 4 methods so you can test other strings easily. The attachment feature of this forum has not been working for me. https://app.box.com/s/dx239343lhwby0zgg5l6

[snb]

or

in C9:E9 =F_snb($A$9,column())

Function F_snb(c00, y)
    F_snb = Split(c00, "_")(UBound(Split(c00, "_")) + y - 5)
    If y = 5 Then F_snb = Val(F_snb)
End Function

If you want my formula treat numbers as number:

in D1: =--LEFT(RIGHT(A4,LEN(A4)-LEN(C6&C8)-26),FIND(".",A4,27+LEN(C6&C8))-27-LEN(C6&C8))

[megha]

All of you are simply awesome out here. Thank you so much! Yes, all of these methods are working well on my long spreadsheet. Ken, thank you so much for the test file. Thank you everyone!