从单元格中提取最后一个子字符串 [英] Extract the last substring from a cell

问题描述

我在列中有名字。我需要将该列的最后一个名称分割成另一列。

姓氏由右侧的空格分隔。

单元格<$ c中的内容$ c> A2 = Alistair Stevens ，我在单元格中输入公式 B2 （我需要'史蒂文生' c $ c>在单元格 B2 ）

我尝试使用以下公式：

  = RIGHT（A2，FIND（，A2,1）-1）
 
 = RIGHT（A2，FIND ，A2））
  

这两个这些公式工作我填充它/复制并粘贴它下面的单元格不起作用。我得到错误的值!!

  A3  - > David Mckenzie 
 
 B3  - > Mckenzie 
  

解决方案

即使有中间名，

  = MID（A2，FIND（CHAR（1），SUBSTITUTE（A2，，CHAR（1），LEN A2）-LEN（SUBSTITUTE（A2，，））））+ 1，LEN（A2））
  

如果你想要所有的东西，最后一个，请查看这个答案。

如果您的姓名中有尾随空格，则可能需要将所有 A2 by TRIM（A2）在上述公式中。请注意，您的第一个公式 = RIGHT（A2，FIND（，A2,1）-1）只是纯粹的机会， Alistair Stevens 的作品。这是因为Alistair和史蒂文生恰好包含相同数量的字符Stevens中的空格）。

I have names in a column. I need to split just the last names from that column into another column.

The last name is delimited by a space from the right side.

The contents in cell A2 = Alistair Stevens and I entered the formula in cell B2 (I need 'Stevens' in cell B2)

I tried using the following formulas:

=RIGHT(A2,FIND(" ",A2,1)-1)

=RIGHT(A2,FIND(" ",A2))

Both these formulas work for this cell but when I fill it down / copy and paste it for the cells below it doesn't work. I get the wrong values!!

A3 -> David Mckenzie

B3 -> Mckenzie

解决方案

This works, even when there are middle names:

=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))

If you want everything BUT the last name, check out this answer.

If there are trailing spaces in your names, then you may want to remove them by replacing all instances of A2 by TRIM(A2) in the above formula.

Note that it is only by pure chance that your first formula =RIGHT(A2,FIND(" ",A2,1)-1) kind of works for Alistair Stevens. This is because "Alistair" and " Stevens" happen to contain the same number of characters (if you count the leading space in " Stevens").

这篇关于从单元格中提取最后一个子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！