Program:Excel 2010
Require:一种将名字、姓氏、电子邮件提取到各个单元格的方法。
Data:我的数据有一个包含原始“脏数据”的表,它是原始的并且一团糟。我用一个简单的方法整理它=IF(A7="Order",1,"")
,然后其余单元格如下:=IF($C7=1,B13,"")
example:
(A) | Orig2 | Header? (C) | NameAll (K) | Price |
----------------------------------------------------------------------------------------
Order | Ms Admin ([email protected] /cdn-cgi/l/email-protection) | =IF(A7="Order",1,"") | =IF($C7=1,B13,"") | =IF($C7=1,B5,"")
Order | Mr Joe ([email protected] /cdn-cgi/l/email-protection) | =IF(A8="Order",1,"") | =IF($C8=1,B14,"") | =IF($C8=1,B6,"")
Order | Ms Fred ([email protected] /cdn-cgi/l/email-protection) | =IF(A9="Order",1,"") | =IF($C9=1,B15,"") | =IF($C9=1,B7,"")
结果是:
(A) | Orig2 | Header? (C) | NameAll (K) | Price |
------------------------------------------------------------------------
Order | Ms Admin ([email protected] /cdn-cgi/l/email-protection) | 1 | Ms Admin ([email protected] /cdn-cgi/l/email-protection) | 13.99
Order | Mr Joe ([email protected] /cdn-cgi/l/email-protection) | 1 | Mr Joe ([email protected] /cdn-cgi/l/email-protection) | 15.99
Order | Ms Fred ([email protected] /cdn-cgi/l/email-protection) | 1 | Ms Fred ([email protected] /cdn-cgi/l/email-protection) | 17.99
Working复制单元格值后,然后在工作表上“复制/值”,我就可以在(U7)
以下内容(从 () 之间提取电子邮件)
=IFERROR(MID(K7,(SEARCH("(",K7)+1),((SEARCH(")",K7)-(SEARCH("(",K7)+1)))),"")
我想做的是从数据移动的一开始就采取它,这样我就可以完全错过将旧数据复制到新数据,然后提取数据的步骤
Tried: failed
=IFERROR($C7=1,B13(MID(B13,(SEARCH("(",B13)+1),((SEARCH(")",B13)-(SEARCH("(",B13)+1))))))
当我试图分离第一个、中间、最后一个(电子邮件)时,我也遇到了诸如 W. H. Minder (m@e.net) 这样的名字的困扰。
我执行多个步骤是否会更容易?
先感谢您。