PDA

View Full Version : [SOLVED] Separating piece of file names into three different column



megha
05-06-2014, 02:21 PM
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
05-06-2014, 02:30 PM
http://www.ultraimg.com/images/Q7eJ.png

Kenneth Hobs
05-06-2014, 02:31 PM
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
05-06-2014, 02:44 PM
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
05-06-2014, 02:45 PM
is this doable without macro?

ashleyuk1984
05-06-2014, 03:54 PM
Yes it is doable without a macro, but it's also handy to have the complete details in the first place :)

Bob Phillips
05-06-2014, 03:59 PM
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,25,99),B2&"_",""))-1)

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

snb
05-07-2014, 12:35 AM
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
05-07-2014, 06:28 AM
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
05-07-2014, 06:52 AM
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
05-07-2014, 06:56 AM
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!