在 Excel 中拆分和分组值

Hi I have a column of values which has different suffix after a dot, i need it to group it based on the value after dot. Example i need to split all values that end with .pdf into one column, values with.xls as another column, etc,,,

如何做到这一点是我的疑问。

拆分和分组

如果您有 Microsoft 365，则可以使用以下内容：

=LET(FileNames,A1:A6,
    FileExtensions,TEXTAFTER(FileNames,"."),
    UniqueFileExtensions,UNIQUE(FileExtensions),
IFERROR(DROP(REDUCE("",UniqueFileExtensions,
    LAMBDA(CurrentResult,UniqueFileExtension,
    HSTACK(CurrentResult,FILTER(FileNames,FileExtensions=UniqueFileExtension)))),,1),""))

如果要添加标题，请添加TOROW and VSTACK:

=LET(FileNames,A1:A6,
    FileExtensions,TEXTAFTER(FileNames,"."),
    UniqueFileExtensions,TOROW(UNIQUE(FileExtensions)),
VSTACK(UniqueFileExtensions,IFERROR(DROP(REDUCE("",UniqueFileExtensions,
    LAMBDA(CurrentResult,UniqueFileExtension,
    HSTACK(CurrentResult,FILTER(FileNames,FileExtensions=UniqueFileExtension)))),,1),"")))

Edit

• 正如 Mayukh Bhattacharya 所建议的，简化为TEXTAFTER.
• 去掉了多余的TOROW从第一个公式。