Question

excel 提取字符串从右向左倒数第三个空格右边的字符

有以下A列数据
A1:PACIFIC COFFEE (AP9) 汇率 6.6833 80.47 4587 94.00(HK)
A2:UUUoo中百超市有限公司 42.00 4587 42.00(CN)
A3:xxx世纪信息技 (APPLE PAY: xxxx) 102.85 4587 102.85(CN)
A4:THE FATTY BAO 汇率 6.6111 393.62 4587 3,872.00(IN)
A5:BARBEQUE NATION PANVEL 汇率 6.6833 579.31 4900 5,655.00(IN)
A6:MANGO PLUS A DIV OF D. 汇率 6.6265 35.19 2084 345.00(CA)
需要提取倒数三组字符串得到
B1: 80.47 4587 94.00(HK)
B2: 42.00 4587 42.00(CN)
B3: 102.85 4587 102.85(CN)
B4: 393.62 4587 3,872.00(IN)
B5: 579.31 4900 5,655.00(IN)
B6: 35.19 2084 345.00(CA)
再使用分列后获得三列数据

或者一步到位为：
C1: 80.47 D1: 4587 E1: 94.00(HK)
C2: 42.00 D2: 4587 E2: 42.00(CN)
C3: 102.85 D3: 4587 E3: 102.85(CN)
C4: 393.62 D4: 4587 E4: 3,872.00(IN)
C5: 579.31 D5: 4900 E5: 5,655.00(IN)
C6: 35.19 D6: 2084 E6: 345.00(CA)

Answer 1

第一种办法，B1单元输入：
=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2)))
下拉公式。
第二种办法，C1单元输入：
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-3+COLUMN(A1))*99,99))
右拉，下拉公式

Answer 2

sub 去空格() dim x as range dim y as range set y = application.inputbox(prompt:="选择需要去掉空格的单元格区域",type:=8) for each x in y x.value=TRIM(x.value) next end sub