Excel获取RPM前的数字

文件名文本处于A2单元格

1
2
=IF(LEN(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))))-LEN(SUBSTITUTE(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))),"-",""))>0,TRIM(RIGHT(SUBSTITUTE(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))),"-",REPT(" ",99)),99)),IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))))

为保证结果类型为数字

1
2
=1*(IF(LEN(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))))-LEN(SUBSTITUTE(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))),"-",""))>0,TRIM(RIGHT(SUBSTITUTE(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))),"-",REPT(" ",99)),99)),IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))))

直接换算为 m/s

1
2
=77*2*Pi()/60000*(IF(LEN(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))))-LEN(SUBSTITUTE(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))),"-",""))>0,TRIM(RIGHT(SUBSTITUTE(IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2))),"-",REPT(" ",99)),99)),IF(LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))>6,RIGHT(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)),LEN(MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))-FIND("-",MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))),MID(LEFT(A2,FIND("RPM",A2)-1),FIND("-",A2)+1,LEN(A2)))))

About

Just a record.

Archives

Recents