Google Sheets – Creating a Parent ID Cell

formulasgoogle sheetsgoogle-sheets-arrayformulavlookup

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.

Best Answer

  • B2: =ARRAYFORMULA(IFERROR(LEFT(A2:A, (FIND(" ", A2:A, 1)-1)), ))
  • C2: =ARRAYFORMULA(IFERROR(MID(A2:A, FIND(" ", A2:A)+1, 256), ))
  • D2: =ARRAYFORMULA(IFERROR(RIGHT(B2:B, LEN(B2:B)-3), ))
  • 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), ))

Related Topic