Extracting infomation from a string

droot · Jan 9, 2019

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!

sandy666 · Jan 9, 2019

is that what you want?

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][td][/td][td=bgcolor:#70AD47]src[/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\Info2\filename_info1_info2.xlsx[/td][/tr]
[/table]

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"src", each Text.AfterDelimiter(_, "\", 3), type text}})
in
    #"Extracted Text After Delimiter"[/SIZE]

droot · Jan 9, 2019

sandy666 said:
is that what you want?

with PowerQuery

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]u:\Folder1\Folder 2\Folder 3\Info1\Info2\filename_info1_info2.xlsx[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Info1\Info2\filename_info1_info2.xlsx[/TD]
[/TR]
</tbody>[/TABLE]

Code:

[SIZE=1]let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"src", each Text.AfterDelimiter(_, "\", 3), type text}}) in #"Extracted Text After Delimiter"[/SIZE]

Not quite, I need 3 columns, Info1, Info2 and Filname (minus _info1_info2).

sandy666 · Jan 9, 2019

sure

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]src.1[/td][td=bgcolor:#70AD47]src.2[/td][td=bgcolor:#70AD47]src.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Info1[/td][td=bgcolor:#E2EFDA]Info2[/td][td=bgcolor:#E2EFDA]filename_info1_info2.xlsx[/td][/tr]
[/table]

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"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"})
in
    #"Split Column by Delimiter"[/SIZE]

btw. don't quote whole post, use just reply

Computerman · Jan 9, 2019

Droot,
Some questions to help clarify your request. Is the u:\folder1\folder2\folder3 ALWAYS the same? Is the Info1 and Info2 in the file path the same as the Info1 and info2 in the file name? Is the filename constructed as you have it written, with the underscores?

Compterman

kensandy1 · Jan 9, 2019

Is this what you are looking for?

XUJwemfP6S8HMHOOozRuPEWOQA-n8jZg6Jt7PPRSQDAiuR7wQQlC8CnTVH8iKOboy8iV-o9_6a5WYplpPWSCcO-EugR63_-t06lLnK5DMwcrdMl_d42tbDXB6OjIr3pmpbl143mqaEl-XyQB-doRuR0DGfrqaTqO-O-H1Sm2T0CUvBPdWKK5nM_J4QKVY0z1wtxYT_6FxKlbzXXoZalglZEYHbvOrc1kPa12v7jxq1uJNgsfUyIbzQaSCZADp38qT1mAgWLzFzB7G4Spf6VXwx54yTcPLO5scvRbgrg0CbRlfZ_CK6J_eaC4R0PraN8DWAVMe0zbbsZ7vEIH7D3t0EVLyUC0obVDDmkiCRAKH84opSttPWgf-9no-4HxJNkjbM3FDgoLglPq-mx23Bx3_cl-P2QaTaMl7QSLDN8HtvW3Omypcr8rHOhyNog0WB0Qq3iDoORE83nWBERXG8wyGrsqqeJIN1EmAUSiETA0a1XMGmrpCWVWlIV5zumovPvWwgv3SOR8aNNM--ICY7rjZPM81l9W-F-Joghx1eF8zBxChAEhdKoDayvkxqeDD9Zg25zZzYdIIfIzGaW9d2xAQmocXcxOoV3RsB1zk6t-JyLjeBQMb0BXZSOFrnzpvhjYjSHDjnPluY31RBD4q-JDPrVk=w540-h198-no

kensandy1 · Jan 9, 2019

kensandy1 said:
Is this what you are looking for?

This is based completely on the file name. If the file name changes in any way, these functions will break.

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

D5 is:
=TRIM(LEFT(SUBSTITUTE(RIGHT(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B2,"",REPT(" ",255)),255)),".",REPT(" ",255)),255)),LEN(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B2,"",REPT(" ",255)),255)),".",REPT(" ",255)),255)))-1-LEN(TRIM(LEFT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B2,"",REPT(" ",255)),255)),".",REPT(" ",255)),255)),"_",REPT(" ",255)),255)))),"_",REPT(" ",255)),255))

F5 is:
=TRIM(RIGHT(SUBSTITUTE(RIGHT(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B2,"",REPT(" ",255)),255)),".",REPT(" ",255)),255)),LEN(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B2,"",REPT(" ",255)),255)),".",REPT(" ",255)),255)))-1-LEN(TRIM(LEFT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B2,"",REPT(" ",255)),255)),".",REPT(" ",255)),255)),"_",REPT(" ",255)),255)))),"_",REPT(" ",255)),255))

sandy666 · Jan 9, 2019

update...

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"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"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]src.1[/td][td=bgcolor:#70AD47]src.2[/td][td=bgcolor:#70AD47]filename[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Info1[/td][td=bgcolor:#E2EFDA]Info2[/td][td=bgcolor:#E2EFDA]filename.xlsx[/td][/tr]
[/table]

droot · Jan 9, 2019

Thanks for this, unfortunately I cannot install power query due to admin rights and it will take a while for it to be approved.

Is there a way to do this without needing to install addins? I'm using Office 2013.

Edit: Just seen the other replies and will test the formula provided

kensandy1 · Jan 9, 2019

As long as the filenames are consistent

foo_heresinfo1_heresinfo2.xlsx
big_bad_wolf.xlsx

where it follows the format for the file is "filename_info1_info2.xlsx" the formulas above should work.

You need the full filepath in a cell though. If you need to use the formula =Cell("filename") those formulas will change slightly.

Extracting infomation from a string

droot

New Member

Excel Facts

sandy666

Banned - Rules violations

droot

New Member

sandy666

Banned - Rules violations

Computerman

Board Regular

kensandy1

New Member

kensandy1

New Member

sandy666

Banned - Rules violations

droot

New Member

kensandy1

New Member

Similar threads

Forum statistics

Share this page

Extracting infomation from a string

New Member

Excel Facts

Banned - Rules violations

New Member

Banned - Rules violations

Board Regular

New Member

New Member

Banned - Rules violations

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock