Excel 2010搜索IF功能中的文本 - 单独的单元格数据 [英] Excel 2010 search for text in IF function - separate cell data
问题描述
程序： Excel 2010
Program: Excel 2010
要求：提取名字，姓氏，电子邮件地址单个细胞。
Require: A way to extract the First Name, Surname, email to individual cells.
数据：我的数据有一个原始脏数据的表，它的原始和混乱。我使用一个简单的 = IF（A7 =Order，1，）整理它，然后剩下的单元格如下： = IF （$ C7 = 1，B13，）
Data: My data has a table with original 'dirty data', it's raw and a mess. I tidy it up using a simple =IF(A7="Order",1,""), then rest of the cells follow: =IF($C7=1,B13,"")
示例：
(A) | Orig2 | Header? (C) | NameAll (K) | Price |
----------------------------------------------------------------------------------------
Order | Ms Admin (e@m.com) | =IF(A7="Order",1,"") | =IF($C7=1,B13,"") | =IF($C7=1,B5,"")
Order | Mr Joe (e@m.com) | =IF(A8="Order",1,"") | =IF($C8=1,B14,"") | =IF($C8=1,B6,"")
Order | Ms Fred (e@m.com) | =IF(A9="Order",1,"") | =IF($C9=1,B15,"") | =IF($C9=1,B7,"")
结果：
(A) | Orig2 | Header? (C) | NameAll (K) | Price |
------------------------------------------------------------------------
Order | Ms Admin (e@m.com) | 1 | Ms Admin (e@m.com) | 13.99
Order | Mr Joe (e@m.com) | 1 | Mr Joe (e@m.com) | 15.99
Order | Ms Fred (e@m.com) | 1 | Ms Fred (e@m.com) | 17.99
工作复制单元格值后， /值'，然后可以在（U7）中使用以下内容（从中提取电子邮件（））
= IFERROR（MID（K7，（SEARCH（ （，K7）+1），（（SEARCH（ ），K7） - （SEARCH（ （，K7）+1））））， ）
Working After I have copied the cell values over, then 'copy/value' on my sheet, I can then use in (U7) the following (which extracts the email from between ())
=IFERROR(MID(K7,(SEARCH("(",K7)+1),((SEARCH(")",K7)-(SEARCH("(",K7)+1)))),"")
我想要做的是从数据移动的开始，所以我可以错过完全复制的步骤旧的，新的，然后数据提取
What I want to do is take it from the beginning of the data moving, so I can miss the step completely of copying the old, to new, then data extraction
试过： 失败
= IFERROR（$ C7 = 1，B13（MID（B13，（SEARCH（ （，B13）+1），（（SEARCH（ ），B13） - （SEARCH（ （，B13 ））））））
我也来了一个名字，如：WH Minder（m@e.net）当尝试分开第一，中，最后（电子邮件）
I also come unstuck with a name such as: W. H. Minder (m@e.net) when trying to separate the First, Middle, Last (email)
我通过多个步骤更容易吗？
Is it just easier that I run through a number of steps?
提前谢谢。
推荐答案
es不是简单的，因为所有的变化。您所显示的数据的一个问题是测试，看看名字是否真的像Ms先生一样。
Parsing names is not simple, because of all the variations. One issue with your data as you've shown it would be testing to see if the First Name was really a title like Mr Ms etc.
我的偏好是做这使用VBA和正则表达式，但这里有一些公式可以玩：
My preference would be to do this using VBA and regular expressions, but here are some formulas you can play with:
Email: =MID(TRIM(A1),FIND("(",TRIM(A1))+1,FIND(")",TRIM(A1))-FIND("(",TRIM(A1))-1)
First Name: =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)
Middle Name: =IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))<>3,"",LEFT(MID(TRIM(A1),FIND(" ",TRIM(A1))+1,99),FIND(" ",MID(TRIM(A1),FIND(" ",TRIM(A1))+1,99))-1))
Last Name: =TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,FIND("(",A1)-1))," ",REPT(" ",99)),99))
名字将是第一个Word，所以你可以测试看看是不是Mr或Mrs或者你不想返回的东西。
The First Name will be the First "Word", so you could test to see if it Mr or Mrs or something you don't want to return.
如果且仅当有三个名字，但是除了First和La之外，它们可以被重写以返回所有的名称st名称
The Middle Name will be returned if and only if there are three names, but that could be rewritten to return all except the First and Last Names
这篇关于Excel 2010搜索IF功能中的文本 - 单独的单元格数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
