调试经验——Excel中查询单元格中最后一个空格后的子字符串（Reverse string search）

需求描述：利用现有query中的字段列表，用Excel公式快速构造主查询所需字段列表，以及对用户更为友好的列别名。现有query的格式为table1.column_ABC as column_ABC，主查询中所需的格式为Query.column_ABC as "column ABC"，可见，两者基本相同，可采用Excel公式快速构建query中字段表（select 到 from中间的50多列）。

问题描述：由于要将列名修改为对用户更为友好的不带下划线的格式，需要提取列名，即，as后的内容。试了一下left, right, substitue等，发现都没有提供从右向左查询的功能。

于是，求助网络，在stackoverflow上找到了方法。

How can I perform a reverse string search in Excel without using VBA?

https://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba

可使用以下公式：

 =IF(ISERROR(FIND(" ",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",
    SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

该作者还解释了一下其工作原理：

LEN(A1)-LEN(SUBSTITUTE(A1," ","")) – Count of spaces in the original string
SUBSTITUTE(A1," ","|", ... ) – Replaces just the final space with a |
FIND("|", ... ) – Finds the absolute position of that replaced | (that was the final space)
Right(A1,LEN(A1) - ... )) – Returns all characters after that |

后面就是常规操作了，示例如下：

SELECT DISTINCT
       QUERY.YRTNUOC
       QUERY.TESSA
       QUERY.AERA
       QUERY."retsulC" AS EMANRETSULC
       QUERY."DI locotorP" AS DI_LOCOTORP
       QUERY."rebmuN etiS ydutS" AS REBMUN_ETIS_YDUTS
       QUERY.yekU AS YEKU
       SCA."DI locotorP_ACS" AS DI_LOCOTORP
       SCA."rebmuN etiS ydutS_ACS" AS REBMUN_ETIS_YDUTS
       SCA.YEKU_ACS AS YEKU
       QUERY."DI_YDUTS_MH" AS DI_YDUTS_MH
       QUERY."DI_ETIS_MH" AS DI_ETIS_MH
       SCA."DI_YDUTS_MH_ACS" AS DI_YDUTS_MH
       SCA."DI_ETIS_MH_ACS" AS DI_ETIS_MH
       QUERY."sailA ydutS" AS SAILA_YDUTS
       QUERY."rotagitsevnI lapicnirP" AS ROTAGITSEVNI_LAPICNIRP
       QUERY."DI NOSREP rotagitsevnI" AS DI_NOSREP_ROTAGITSEVNI
       QUERY."sutats eripsnI" AS SUTATS_ERIPSNI
       QUERY."ytiC" AS YTIC
       QUERY."etatS" AS ETATS
       QUERY."edoC tsoP" AS EDOC_TSOP
       SCA."ACS" AS ACS
       QUERY."detacollA rentraP" AS DETACOLLA_RENTRAP
       QUERY."sutatS ydutS" AS SUTATS_YDUTS
       QUERY."sutatS etiS" AS SUTATS_ETIS
       QUERY."epyT ydutS" AS EPYT_YDUTS
       QUERY."esahP ydutS" AS ESAHP_YDUTS
       QUERY."tinU ssenisuB" AS TINU_SSENISUB
       QUERY."ecruoS gnidnuF" AS ECRUOS_GNIDNUF
       QUERY."noisiviD ydutS" AS NOISIVID_YDUTS
       QUERY."noisiviD spOveD" AS NOISIVID_SPOVED
       QUERY."aerA citaeparehT" AS AERA_CITAEPAREHT
       QUERY."noitpircseD trohS etadidnaC" AS NOITPIRCSED_TROHS_ETADIDNAC
       QUERY."snoitacidnI yramirP etadidnaC" AS SNOITACIDNI_YRAMIRP_ETADIDNAC
       QUERY."galF cirtaideP" AS GALF_CIRTAIDEP
       QUERY."yrogetaC elanoitaR ssenisuB" AS YROGETAC_ELANOITAR_SSENISUB
       QUERY."DITN LSCR" AS DITN_LSCR
       QUERY."LSCR" AS LSCR
       QUERY."ytiC LSCR" AS YTIC_LSCR
       QUERY."etatS LSCR" AS ETATS_LSCR
       QUERY."DITN LSCR S" AS DITN_LSCR_S
       QUERY."LSCR S" AS LSCR_S

这样，一段长达1800字的query就被快速编写出来了，Excel依然是如此强大，哈哈！

其实，该公式可以获取单元格中任意指定的一个字符最后一次出现后的子字符串，如，

A1单元格内容：SAMPLETABLE.SampleColum CongratulationsPD, great job!

使用公式=IF(ISERROR(FIND("s",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",
    SUBSTITUTE(A1,"s","|",LEN(A1)-LEN(SUBSTITUTE(A1,"s",""))))))后的结果为：

PD, great job!

特此感谢StackoverFlow，感谢无数网友的帮助。这也是为什么我要在深夜端坐电脑前写下这篇文章的动力，不能只知索取，不知回报！