Pulling Info from Filename into Cell

[Desu Nota from Columbus]

I have files that were generated by three different people (Fred, Josh, Tiffany) with his/her name saved within the file name (see examples below).

I need a formula that pulls out the person's name and adds it to all rows that has data in it. Let us assume that we are adding this into A3 and continuing down the column until there are no more records.

The file names are the following:

Fred 1 EP Line 1
Fred 1 EP Line 10
Fred 2 EP Line 1
Fred 2 EP Line 10

Tiffany 1 EP Line 1
Tiffany 1 EP Line 10
Tiffany 2 EP Line 1
Tiffany 2 EP Line 10

Josh 1 EP Line 1
Josh 1 EP Line 10
Josh 2 EP Line 1
Josh 2 EP Line 10


There is data in columns B-AB, and I need the person's name added to Column A for each row with data.

Ex. For file Josh 1 EP Line 1 in column A, row 3 (or A3) it should say Josh. Then I want to copy this formula all the way down column A filling in Josh from the filename for all records.




Hopefully Ive explained what I am looking to do with enough detail. I haven't been able to figure out how to deal with the varying length of the file names.

The files are stored in the same folder, so they have a similar path until the actual names.

<table border="0" cellpadding="0" cellspacing="0" width="533"><colgroup><col width="533"></colgroup><tbody><tr height="17"> <td class="xl22" style="height:12.75pt;width:400pt" height="17" width="533">\\Epfiles\qa minolta\Minolta\Gauge R & R Study\Fred's Iteration Info\Fred 1 EP Line 1.txt</td> </tr></tbody></table>

 

[John Davis]

I have files that were generated by three different people (Fred, Josh, Tiffany) with his/her name saved within the file name (see examples below).

I need a formula that pulls out the person's name and adds it to all rows that has data in it. Let us assume that we are adding this into A3 and continuing down the column until there are no more records.

The file names are the following:

Fred 1 EP Line 1
Fred 1 EP Line 10
Fred 2 EP Line 1
Fred 2 EP Line 10

Tiffany 1 EP Line 1
Tiffany 1 EP Line 10
Tiffany 2 EP Line 1
Tiffany 2 EP Line 10

Josh 1 EP Line 1
Josh 1 EP Line 10
Josh 2 EP Line 1
Josh 2 EP Line 10


There is data in columns B-AB, and I need the person's name added to Column A for each row with data.

Ex. For file Josh 1 EP Line 1 in column A, row 3 (or A3) it should say Josh. Then I want to copy this formula all the way down column A filling in Josh from the filename for all records.




Hopefully Ive explained what I am looking to do with enough detail. I haven't been able to figure out how to deal with the varying length of the file names.

The files are stored in the same folder, so they have a similar path until the actual names.

<table border="0" cellpadding="0" cellspacing="0" width="533"><colgroup><col width="533"></colgroup><tbody><tr height="17"> <td class="xl22" style="height:12.75pt;width:400pt" height="17" width="533">\\Epfiles\qa minolta\Minolta\Gauge R & R Study\Fred's Iteration Info\Fred 1 EP Line 1.txt</td> </tr></tbody></table>

Not exactly what you want but maybe you can modify/paste special-values ect to suit your purpose:

=CELL("filename",A1) - will give you the full path and filename for A1

Just an idea.

 

[Desu Nota from Columbus]

Thanks for the reply, but I have already solved my problem. I changed all names to 4 digits (Tiffany-->Tiff) and made all the Line #'s two digits.

I used the =cell("filename"), then I used the =mid() to get the name.