• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need formula

Balajisx

Member
Hi All,

Please help me to get the formula for get the middle word which I attached in the template.

I have a some source file in the column a i need to identify the name of the file which is at the middle of the path. The length and date everthing will change day by day.. However I need a dynamic formula to find the name. Please help
 

Attachments

  • name.xlsx
    9.1 KB · Views: 11
Hi,

Here is a VBA based solution:
Code:
Sub GetFileNameFromString()

    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        Dim fileName As String
        fileName = Right(c, Len(c) - InStrRev(c, "\\") - 1)
        fileName = Replace(fileName, Right(fileName, Len(fileName) - InStrRev(fileName, " 20") + 1), "")
        c.Offset(, 1) = fileName
    Next c
  
End Sub

Assuming the year is always 20**
 
Thank you all for your reply. it is working fine for few rows.. here is an another set of file format i have.. I am getting value errors for that. Please advise
 

Attachments

  • name1.xlsx
    25.9 KB · Views: 6
Thank you all for your reply. it is working fine for few rows.. here is an another set of file format i have.. I am getting value errors for that. Please advise
Hi,

Well, with the code approach you can simply change "\\" to "\" and remove the "-1":
Code:
Sub GetFileNameFromString()

    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        Dim fileName As String
        fileName = Right(c, Len(c) - InStrRev(c, "\"))
        fileName = Replace(fileName, Right(fileName, Len(fileName) - InStrRev(fileName, " 20") + 1), "")
        c.Offset(, 1) = fileName
    Next c
 
End Sub
 
Thank you all for your reply. it is working fine for few rows.. here is an another set of file format i have.. I am getting value errors for that. Please advise
Why did you not submit a sample representing complete data? Something like below should work for you:
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,SEARCH(" 20??-",A2,1)),"\",REPT(" ",99)),":",REPT(" ",99)),99))
 
Hi All,

Thank you for all you reply. Here I have an other senerio where I need to split the batch name. Request your assistance in this as well. Attached is the file for your review. Column which is highlighed in yellow is a batch name which i am able to find by modifying the formula. However I need to find out the batch id in column C. I am looking for the formula to split it.
the correct answer is given in column D.
 

Attachments

  • sample.xlsb
    8.5 KB · Views: 5
Thank you for your reply.
This is just a sample. The batch name will not always begin with PA and the lenght of the path will change. Could you please help me with a dynamic formula
 
Thank you for your reply.
This is just a sample. The batch name will not always begin with PA and the lenght of the path will change. Could you please help me with a dynamic formula
Why not post more samples representative of your complete data as has been belabored before?

Having said that a formula like below should be able to extract desired information, provided that:
- Your data always contains "\\" as path separator.
- Batch Name and Batch ID is separated by "-"
- Batch Name and ID field is pre-penultimate folder.

Batch name:
=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"\\",REPT(" ",199),(LEN(A2)-LEN(SUBSTITUTE(A2,"\\","")))/2),"\\",REPT(" ",199),((LEN(A2)-LEN(SUBSTITUTE(A2,"\\","")))/2)-1),199,199)),"-",REPT(" ",199)),199))

Batch ID:
=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"\\",REPT(" ",199),(LEN(A2)-LEN(SUBSTITUTE(A2,"\\","")))/2),"\\",REPT(" ",199),((LEN(A2)-LEN(SUBSTITUTE(A2,"\\","")))/2)-1),199,199)),"-",REPT(" ",199)),199))
 
Thank you Shri. It is working for few lines.. but for few lines I am getting the batch id as input. Please assist. attached is the sample
 

Attachments

  • sample 2.xlsx
    9.3 KB · Views: 5
Thank you Shri. It is working for few lines.. but for few lines I am getting the batch id as input. Please assist. attached is the sample
This sample is failing in the following condition:
- Batch Name and ID field is pre-penultimate folder.
You need to tell us how many more such combinations exist?

Will this portion always be there [000]- in the part to be extracted?
 
yes this piece always will be there [000]- Batch id would be after [000]- and before \\Input\\
Try
Batch Name:
=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,"\\",REPT(" ",199)),FIND("[000]-",SUBSTITUTE(A2,"\\",REPT(" ",199)))-99,199)),"[000]-","[000]"&REPT(" ",199)),199))

Batch Id:
=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,"\\",REPT(" ",199)),FIND("[000]-",SUBSTITUTE(A2,"\\",REPT(" ",199)))-99,199)),"[000]-","[000]"&REPT(" ",199)),199))
 
Another option.

1] Batch Name :

=MID(A2,FIND("y\\",A2)+3,FIND("]",A2)-FIND("y\\",A2)-2)

2] Batch ID :
=MID(A2,FIND("]",A2)+2,FIND("\\I",A2,FIND("\\I",A2)+1)-FIND("]",A2)-2)

Regards
 
Back
Top