I've got a spreadsheet of various categories and I'd like to create a column which gets the parent name of that particular category.

They are currently broken down as follows:

01.01 Category 1
01.02 Category 1a
01.02.01 Category 1aa
01.02.02 Category 1ab
01.02.03 Category 1ac
01.03 Category 1c

I've put the numbers into a seperate column using =LEFT(A2,(FIND(" ",A2,1)-1))

and I've put the names of the categories into another column using =MID(A2,FIND(" ",A2)+1,256)

but I've not managed to get the parent name into a column yet so was hoping someone could help.

  • B2: =ARRAYFORMULA(IFERROR(LEFT(A2:A, (FIND(" ", A2:A, 1)-1)), ))
  • C2: =ARRAYFORMULA(IFERROR(MID(A2:A, FIND(" ", A2:A)+1, 256), ))
  • E2: =ARRAYFORMULA(IFERROR(IF(LEN(B2:B)>3, LEFT(B2:B, 5), ), ))
  • F2: =ARRAYFORMULA(IFERROR(VLOOKUP( IFERROR(IF(LEN(B2:B)>3, LEFT(B2:B, 5), ), ), {IFERROR(IF(LEN(B2:B)>3, LEFT(B2:B, 5), ), ), IFERROR(MID(A2:A, FIND(" ", A2:A)+1, 256), )}, 2, 0), ))

  • Brilliant thanks, i managed to get the sub id and parent id last night but the parent name bit was really baffling me. – NewToCode Feb 12 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.