Extracting infomation from a string

[droot]

Hi,

I have a list of paths that I need to extract infomation from -

<colgroup><col width="625"></colgroup><tbody>
[TD="width: 625"]u:\Folder1\Folder 2\Folder 3\Info1\Info2\filename_info1_info2.xlsx
[/TD]

</tbody>

What I need to extract is Info1, Info2 and just the "filename" into individual cells.


I've tried various left right combinations but the Info1,2 and filenames aren't consistent so it might work for 10 pathways but it won't work for the other 30k.

=(RIGHT(C2,LEN(C2)-SEARCH("Info2",C2)-6)) - This is what I tried to extract the filename but having the problem described above. I was planning on using a substitute formula to remove the "info1_info2" from the result to get the part I need. I imagine this isn't the most efficient way to do this.

Hope this made sense and any help will be greatly appreciated.

Thanks!

 

[droot]

Folder 1 2 and 3 are always the same but Info1 and 2 change and yes the filename is the way I have written it.

 

[droot]

I've tried the formulas you have provided and got the following results -

1st forumla - "U:\Folder" (missing the second part of folder 1 name)

2nd Formula - "(part of) Folder 1\Folder 2\Folder 3\Info1\Info2"

3rd Formula - "info2" (from filename_info1_info2)

 

[droot]

"-" is used for spacing in the filename.

eg. big-bad-wolf_info1_info2.xlsx

 

[kensandy1]

The formula is using the characters _ . \ to parse the string. so if those aren't in the file name, the formula won't work.

I copied the wrong formula for the filename earlier, this should be it.

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B2,"",REPT(" ",255)),255)),".",REPT(" ",255)),255)),"_",REPT(" ",255)),255)),"",REPT(" ",255)),255))

The - should be included in the output of the filename.

 

[droot]

This is the output for that formula -
[TABLE="width: 439"]
<colgroup><col width="439"></colgroup><tbody>[TR]
[TD="width: 439"][/TD]
[/TR]
</tbody>[/TABLE]
"u:\Folder1\Folder 2\Folder3\info1\info2\file-name

The "_info1_info2.xlsx" has been removed.


Maybe this will better explain the path format

[TABLE="width: 625"]
<colgroup><col width="625"></colgroup><tbody>[TR]
[TD="width: 625"]u:\public_folder\company name 2019\Folder3\info 1\Info_2\client-name-here_info1_info2.xlsx


[/TD]
[/TR]
</tbody>[/TABLE]

 

[droot]

Somehow managed to get permission to install power query sooner than expected.

I've renamed the path column to src but i'm getting the following - Expression.Error: The column 'src' of the table wasn't found. Details: src

 

[droot]

replaced "source" with the sheet name and now have the following error - Expression.Error: A cyclic reference was encountered during evaluation.

Sorry, I've never used this before so probably missing something obvious.

let
Source = Excel.Workbook(File.Contents("C:\Users\droot\Desktop\US_clients.xlsx"), null, true),
#"Extracted Text After Delimiter" = Table.TransformColumns(deduped, {{"src", each Text.AfterDelimiter(_, "", 3), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "src", Splitter.SplitTextByDelimiter("", QuoteStyle.Csv), {"src.1", "src.2", "src.3"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Split Column by Delimiter", "Text Before Delimiter", each Text.BeforeDelimiter([src.3], "_"), type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([src.3], "."), type text),
#"Merged Columns" = Table.CombineColumns(#"Inserted Text After Delimiter",{"Text Before Delimiter", "Text After Delimiter"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"filename"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"src.3"})
in
#"Removed Columns"

 

[droot]

OMG managed to make this work somehow. Power query is amazing!

 

[sandy666]

LOL IT agreed with PowerQuery? Impossible!
Great you find it useful...

maybe shorter version....

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "src", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"src.1", "src.2", "src.3", "src.4", "src.5", "src.6", "src.7"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"src.5", "src.6", "src.7"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Other Columns", {{"src.7", each Text.BeforeDelimiter(_, "_"), type text}})
in
    #"Extracted Text Before Delimiter"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][td][/td][td=bgcolor:#70AD47]src.5[/td][td=bgcolor:#70AD47]src.6[/td][td=bgcolor:#70AD47]src.7[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]u:\Folder1\Folder 2\Folder 3\Info1\Info2\filename_info1_info2.xlsx[/td][td][/td][td=bgcolor:#E2EFDA]Info1[/td][td=bgcolor:#E2EFDA]Info2[/td][td=bgcolor:#E2EFDA]filename[/td][/tr]

[tr=bgcolor:#FFFFFF][td]u:\public_folder\company name 2019\Folder3\info 1\Info_2\client-name-here_info1_info2.xlsx[/td][td][/td][td]info 1[/td][td]Info_2[/td][td]client-name-here[/td][/tr]
[/table]