parse file name

[DHolcombe]

Hi, I have a situation and not sure the best most efficient way to parse a file name. I have a directory of files i need to process and i need to collect information from each file name before i process each file.

Below are two examples of file names:
sec_abcdef_lg6789_david_ted
ext_jurtyu_pmd998_9259_wendy H_ted


The file names have the following naming convention:
Each file name contains the follow information:
section_part_loginfo_name_ted

section and part are the first two pieces of information
name and ted are the last two pieces of information
loginfo is after the "part" but before the "name"

for the examples above:
loginfo: lg6789
loginfo: pms998_9259


Note: i would have posted a file but i was not sure how to post a file.

 

[myall_blues]

What version of Excel are you using? Can you update your profile with that information?

Also, can you show what output/result you are hoping for?

 

[DHolcombe]

i am using microsoft office home and student 2016 (at home) and Microsoft 365 at work

Output is to parse file name into the respective pieces. So for each file name there were be
section_txt = "sec"
part_txt = "abcdef"
loginfo_txt = "lg6789"
name_txt = "david"
ted_txt = "ted"

 

[Joe4]

i am using microsoft office home and student 2016 (at home) and Microsoft 365 at work

Please update your account details. That way it will show in your details and you won't need to explicitly mention it in every thread/question.
Click on this link to edit your account details: Account details (and don't forget to click "Save" at the bottom).

 

[myall_blues]

Maybe something like this for 365, for examples shown? If your loginfo has more than one underscore this won't work.

Book1
	A	B	C	D	E	F	G	H	I
1
2	sec_abcdef_lg6789_david_ted				sec	abcdef	lg6789	david	ted
3	ext_jurtyu_pmd998_9259_wendy H_ted				ext	jurtyu	pmd998_9259	wendy H	ted
4
Sheet1

Cell Formulas
Range	Formula
E2:I3	E2	=SUBSTITUTE(TEXTSPLIT(IF(LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))=4,A2,SUBSTITUTE(A2,"_",";",3)),"_"),";","_")
Dynamic array formulas.

 

[Eric W]

Here's how to do it with just 2016 functions:

Book1
	A	B	C	D	E	F	G
1			section	part	loginfo	name	ted
2	sec_abcdef_lg6789_david_ted		sec	abcdef	lg6789	david	ted
3	ext_jurtyu_pmd998_9259_wendy H_ted		ext	jurtyu	pmd998_9259	wendy H	ted
Sheet7

Cell Formulas
Range	Formula
C2:C3	C2	=TRIM(LEFT(SUBSTITUTE(A2,"_",REPT(" ",99)),99))
D2:D3	D2	=TRIM(MID(SUBSTITUTE(A2,"_",REPT(" ",99)),100,99))
E2:E3	E2	=SUBSTITUTE(SUBSTITUTE(A2,C2&"_"&D2&"_",""),"_"&F2&"_"&G2,"")
F2:F3	F2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,"_",REPT(" ",99)),200),99))
G2:G3	G2	=TRIM(RIGHT(SUBSTITUTE(A2,"_",REPT(" ",99)),99))