Copy cell values based on criteria into new column, please help!

**avoss** · 02-06-2013, 12:46 PM

Hi everyone!

I am working on this spreadsheet (I attached an example below). I want to see if there is a way for excel to copy all the 7 digit codes into the 7 digit column, 8 digit codes into 8 digit column, and so on.

I tried using advanced filter and lookups but to no success. The sheet currently reads column B "Codes" and returns the amount of digits to column C "#Digits", I thought that might be helpful but I can't figure out what to do now. Any help would be very appreciated!

Thanks again!

Code Help.xlsx

**benishiryo** · 02-06-2013, 09:47 PM

hi avoss, welcome to the forum. for columns E to G, try this array formula:

Formula:

Please Login or Register  to view this content.

in column H, try:

Formula:

Please Login or Register  to view this content.

or without depending on column C, try this for E to G:
=IFERROR(INDEX($B$2:$B$20,SMALL(IF(LEN($B$2:$B$20)=(LEFT(E$1,FIND(" ",E$1)-1))+0,ROW($B$2:$B$20)-ROW($B$2)+1),ROWS(E$2:E2))),"")

and for H:

Formula:

Please Login or Register  to view this content.

**for all these formulas, you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it

**avoss** · 02-07-2013, 11:30 AM

Thank you so much for your help benishiryo! The formula works for column E and column H, but when I use the formula in F and G it returns the 7 digit values that are in column E. Do you know how I can fix this?

Thanks again!