[퀴즈_130] 4자리수로 바꾸기

=LET(
raw, C4:C21,
r, FILTER(raw, raw<>""),
fn, TRIM(TEXTBEFORE(r, "층")),
rn, TRIM(TEXTBEFORE(TEXTAFTER(r, "층"), "호")),
TEXT(fn, "0000") & "층 " & TEXT(rn, "0000") & "호"
)

=LET(
r, C4:C21,
fn, REGEXEXTRACT(r, "\d+(?=층)"),
rn, REGEXEXTRACT(r, "\d+(?=호)"),
TEXT(fn, "0000") & "층 " & TEXT(rn, "0000") & "호"
)

TEXT(LEFT(C4,FIND("층",C4)-1),"0000")&"층 "&TEXT(MID(C4,FIND(" ",C4)+1,FIND("호",C4)-FIND(" ",C4)-1),"0000")&"호"

​

파워쿼리로는

Text.PadStart([목록], 4, "0")

=BYROW(C4:C21,
LAMBDA(r, TEXTJOIN(" ",,TEXT(REGEXEXTRACT(r,"\d+",1)*1,"0000")&{"층","호"})))

=BYROW(C4:C21,
LAMBDA(r, TEXTJOIN(" ",,TEXT(TEXTSPLIT(r,{"층"," ","호"},,1)*1,"0000")&{"층","호"})))

고수님들이 정말 많네요 ㅜ 부럽습니다

=RIGHT("0000"&TEXTBEFORE(A1:A18," "),5)&" "&
  RIGHT("0000"&TEXTAFTER(A1:A18," "),5)

=LET(D,RIGHT("0000"&REGEXEXTRACT(A1:A18, {"(\d+)층","(\d+)호"},1),5), 
INDEX(D,,1)&" "&INDEX(D,,2))

=REGEXREPLACE(
  REGEXREPLACE($A$1:$A$18,"(\d+)(?=(층|호))","0000$1"),
"\K0+(?=\d{4}(층|호))","")

=REGEXREPLACE(
"0000"&SUBSTITUTE($A$1:$A$18," ", " 0000"),
"\K0+(?=\d{4}(층|호))","")

​