How to Clean up Excel Sheet

[mn072065]

Some help i have a movie library that i want to clean up and export to a movie catalog program. Here is what im looking for, i want if its possible to just have the movie name showing, for example below the movie "And justice for all", just want that to show on my sheet, not the other and justice for all, another one "Resting Place" and that's it, not the other stuff. Is this possible. If you help please show me in details since im a rookie. Thanks again for your valuable time.

File name
Perfume Story of a Murderer.iso
Resting Place (1986).iso
Resting Place (1986).jpg
Resting Place (1986).nfo
RESTING PLACE-poster.jpg
Al Pacino
...And Justice for All. (1979).iso
...And Justice for All. (1979).jpg
...And Justice for All. (1979).nfo
...And Justice for All. (1979).png
3 Godfathers (1948).nfo
88 Minutes (2008).iso
88 Minutes (2008).jpg
88 Minutes (2008).nfo
88 Minutes (2008).png
88 MINUTES-disc.png
88 MINUTES-fanart.jpg
88 MINUTES-fanart1.jpg
88 MINUTES-fanart2.jpg
88 MINUTES-landscape.jpg
88 MINUTES-logo.png
88 MINUTES-poster.jpg
AND JUSTICE FOR ALL-fanart1.jpg
AND JUSTICE FOR ALL-fanart2.jpg
AND JUSTICE FOR ALL-poster.jpg
Angels in America (2003).CD1.iso
Angels in America (2003).CD1.jpg
Angels in America (2003).CD2.iso
Angels in America (2003).nfo
ANGELS IN AMERICA PART 2-fanart1.jpg
ANGELS IN AMERICA PART 2-fanart2.jpg
ANGELS IN AMERICA PART 2-poster.jpg

[Glenn Kennedy]

It wasn't clear exactly what output you wanted...

Try this array formula in D2

=IFERROR(INDEX(A:A,SMALL(IF(RIGHT($A$2:$A$33,3)="nfo",ROW($A$2:$A$33)),ROWS(D$2:D2))),"")



Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

Actually, this is better:

=IFERROR(LEFT(INDEX(A:A,SMALL(IF(RIGHT($A$2:$A$33,3)="nfo",ROW($A$2:$A$33)),ROWS(D$2:D2))),LEN(INDEX(A:A,SMALL(IF(RIGHT($A$2:$A$33,3)="nfo",ROW($A$2:$A$33)),ROWS(D$2:D2))))-4),"")

[mn072065]

ok im sorry i see it works, wow , but how do i copy this formula all the way down to end of sheet, and will this work on any of the other spreadsheets,

[Glenn Kennedy]

Just drag the formula down as far as you need.

I do not understand what you are asking when you refer to other spreadsheets.

Please explsin in detail.

[mn072065]

Sorry I have 6 more spredsheets with the same structure so i'm assuming i can use this formula with those spreadsheets, and i tried to drag formula, nothing shows. Thanks again.

[mn072065]

i want to make sure im doing correctly, i copy formula, then go to my spreadsheet and paste, then i hit CTRL+SHIFT+ENTER, i tied and only first movie corrected shows, but when i drag i see nothing. Thanks again

[Glenn Kennedy]

In what cell is the formula?

[Glenn Kennedy]

Did you adjust the formula ranges to suit your actual data?

[mn072065]

formula in cell b3

[mn072065]

shall i send you spreadsheet

[Glenn Kennedy]

Did you adjust the formula ranges to suit your actual data?

[Glenn Kennedy]

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[mn072065]

i think i did it correctly, it said uploaded. then i closed the window

[mn072065]

ok sorry, will do next time

[Glenn Kennedy]

I will be back at pc in 30 mins.

[AliGW]

It works if you adapt the formula, as Glenn has told you:

=IFERROR(LEFT(INDEX(A:A,SMALL(IF(RIGHT($A$2:$A$4714,3)="nfo",ROW($A$2:$A$4714)),ROWS(D$2:D2))),LEN(INDEX(A:A,SMALL(IF(RIGHT($A$2:$A$4714,3)="nfo",ROW($A$2:$A$4714)),ROWS(D$2:D2))))-4),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[mn072065]

Thank You AliGW, but when do i hit CTRL+SHIFT+ENTER, cause i'm doing that, i copy formula, then go to column B2, then i hit CTRL+SHIFT+ENTER, nothing happens, then paste, but when i drag down to fill, its blank, and thank you also for your valuable time.

[Glenn Kennedy]

1. use this array formula:

=IFERROR(TRIM(LEFT(INDEX(A:A,SMALL(IF(RIGHT($A$2:$A$4714,3)="nfo",ROW($A$2:$A$4714)),ROWS(B$2:B2))),LEN(INDEX(A:A,SMALL(IF(RIGHT($A$2:$A$4714,3)="nfo",ROW($A$2:$A$4714)),ROWS(B$2:B2))))-4)),"")

for two reasons. Firstly it removes a pile of extra spaces in a lot of your titles. Secondly, for this type of formula it is good practice to use the starting point (B2) as the basis of the range in this part of the formula ROWS(B$2:B2).

The reason it is blank is that you have upset the array.

Once the formula is entered in B" and CTRL-SHIFT-ENTER used, do NOT double click the cell. Click on it ONCE and drag down. It works perfectly. If you double click it, or otherwise upset the array the {} will disappear from the formula and it stops working.

[mn072065]

I figured out thank you all so much really, will try on the other sheets, you guys are the best.

[mn072065]

George if i have issues with the other sheets, would you mind helping me again please.

[AliGW]

Who is George?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Glenn Kennedy]

My name's Glenn.... happy to help further, if needed.

Here's a non array version. Just use enter:

=IFERROR(TRIM(LEFT(INDEX(Sheet1!A:A,SMALL(INDEX((RIGHT($A$2:$A$4714,3)<>"nfo")*10^10+ROW($A$2:$A$4714),0),ROWS(D$2:D2))),LEN(INDEX(Sheet1!A:A,SMALL(INDEX((RIGHT($A$2:$A$4714,3)<>"nfo")*10^10+ROW($A$2:$A$4714),0),ROWS(D$2:D2))))-4)),"")

For now...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Glenn Kennedy]

There are plently of occasions where people have called me Glenn Campbell... I've been called George here twice in the last few days...

[mn072065]

Please forgive me Glenn i was actually texting my boss George and i typed his name instead, so sorry, lol. Your the best.

[AliGW]

LOL!!!

[Glenn Kennedy]

Different!!

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.