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 :)

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)

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

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!

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.