การสร้างที่อยู่อีเมลจากคอลัมน์เดียวด้วยการเติมแบบแฟลช, สูตรข้อความ &คำแนะนำสูตรข้อความของผู้วิจารณ์

การใช้แฟลชเติม

นี่เป็นคำถามที่มักถูกถามถึงวิธีการดึงข้อมูลจากคอลัมน์หนึ่งไปยังอีกรูปแบบหนึ่งหรือมากกว่าหนึ่งคอลัมน์

เราจะไปดูวิธีการทำสิ่งนี้โดยใช้คุณสมบัติการเติมแฟลชก่อน เติมแฟลช เป็นคุณลักษณะใหม่ที่เพิ่มลงใน Microsoft Excel 2013 และเวอร์ชันที่ใหม่กว่า

ในตัวอย่างด้านล่าง เรามีรายชื่อและนามสกุลในคอลัมน์ A การเติมแบบรวดเร็วจำเป็นต้องมีคอลัมน์ต้นทางเสมอ เพื่อให้ทำงานได้ ซึ่งในกรณีนี้คือคอลัมน์ A ซึ่งมีชื่อพนักงาน มักใช้ Flash Fill ในตัวอย่างเพื่อสร้างที่อยู่อีเมลหรือข้อมูลสูตรซ้ำประเภทอื่นๆ สูตรข้อความยังใช้กันอย่างแพร่หลายสำหรับคำถามเกี่ยวกับการสร้างอีเมล มีสูตรข้อความหลากหลายรูปแบบที่ใครๆ ก็ใช้ได้ ตั้งแต่แบบซับซ้อนไปจนถึงแบบเรียบง่าย ในบทความนี้ เรามีตัวอย่างการสร้างอีเมลแบบเติมแฟลช ตัวอย่างการสร้างอีเมลสูตรข้อความ และเราได้อัปเดตด้วยคำแนะนำสูตรข้อความของผู้วิจารณ์

1) เราต้องการเติมคอลัมน์ B ด้วยที่อยู่อีเมลของพนักงาน วิธีสร้างที่อยู่อีเมลโดยฝ่ายไอทีภายในของบริษัท คือ ชื่อและนามสกุลของพนักงานจะถูกรวมเข้ากับชื่อโดเมนของบริษัท เพื่อสร้างที่อยู่อีเมล

2) ในเซลล์ B5 เราต้องให้ Flash Fill ซึ่งเป็นตัวอย่างของสิ่งที่เราต้องการก่อน จากนั้น Flash Fill จะใช้สิ่งนี้เป็นเทมเพลตเพื่อเติมข้อมูลในคอลัมน์ที่เหลือ ดังนั้นเราจึงป้อนข้อความ:

admin@wsxdn.com

จากนั้นให้กด CTRL-ENTER

3) หลังจากกด CTRL-ENTER หนึ่งครั้ง Excel จะเปลี่ยนข้อความเป็นไฮเปอร์ลิงก์โดยอัตโนมัติ

4) จากนั้นเราต้องไฮไลต์ช่วงทั้งหมด B5:B15 ที่เราต้องการเติมด้วยที่อยู่อีเมลโดยใช้ Flash Fill แล้วกด CTRL-E ซึ่งเป็นปุ่มลัดสำหรับ Flash Fill หรือช่วงที่ไฮไลต์ให้ไปที่ Data จากนั้นคลิกที่ Flash Fill ตามที่แสดง

5) ส่วนที่เหลือของช่วงจะเต็มไปด้วยที่อยู่อีเมลที่จำเป็น

อ่านเพิ่มเติม: การแยกข้อความใน Excel โดยใช้ Flash Fill

การใช้สูตร TEXT

ทั้งหมดจะไม่สูญหายหากคุณมี Microsoft Excel เวอร์ชันก่อนหน้า เนื่องจากเราสามารถทำสิ่งเดียวกันกับสูตร TEXT ได้ การใช้สูตร TEXT นั้นซับซ้อนกว่าเล็กน้อย อย่างไรก็ตาม ในกรณีนี้

1) ประการแรก เราต้องดูที่ข้อความในคอลัมน์ A เพื่อหาว่าจะใช้ฟังก์ชัน TEXT ใดในสูตรที่ซับซ้อนของเรา

2) เราเห็นว่าเรามีชื่อ ตามด้วยเว้นวรรค แล้วก็นามสกุล

3) เราจะสร้างสูตรนี้ตั้งแต่เริ่มต้น

อย่างแรก เราใช้ฟังก์ชัน SEARCH เพื่อค้นหาช่องว่าง และในเซลล์ B5 เราป้อน:

=ค้นหา (” “, A5, 1)

กด CTRL-ENTER

4) หลังจากกด CTRL-ENTER เราจะได้ 5 หรืออีกนัยหนึ่งในกรณีของ Emma Smith ช่องว่างคืออักขระที่ห้า การดับเบิลคลิกแล้วส่งสูตรลงไปจะแสดงให้เราเห็นว่าพื้นที่ว่างสำหรับพนักงานคนอื่นๆ ทั้งหมดอยู่ที่ใด สำหรับนิโคล โรเบิร์ตส์ ช่องว่างคืออักขระตัวที่เจ็ดในสตริงข้อความตามที่แสดง

5) ตอนนี้เราสามารถใช้ฟังก์ชัน LEFT เพื่อแยกชื่อได้ เนื่องจากเราสามารถแยกอักขระทั้งหมด ทางซ้ายของช่องว่างเพื่อให้ได้ชื่อตามพื้นที่ที่ว่างได้

ดังนั้น เราต้องกำหนดให้ฟังก์ชัน LEFT เป็นสตริงข้อความป้อนเข้า จากนั้นจึงระบุตัวเลขของอักขระในสตริงข้อความจากด้านซ้ายที่เราต้องการแยก

ดังนั้นเราจึงเลือกเซลล์ A5 เป็นสตริงข้อความ จากนั้นเพื่อค้นหาจำนวนอักขระที่จะแยกจากด้านซ้ายเพื่อให้ได้ชื่อเท่านั้น เราใช้ฟังก์ชัน SEARCH และลบ 1 เพื่อรับ:

=ซ้าย (A5, ค้นหา (” “, A5, 1) -1)

เราลบ 1 เพราะเราไม่ต้องการเว้นวรรค

6) เมื่อเรากด CTRL-ENTER แล้วดับเบิลคลิกที่สูตรแล้วส่งลงไป เราดึงชื่อทั้งหมดออกมาตามที่แสดง

7) ขณะนี้เราสามารถคัดลอกสูตรโดยกด CTRL-C สองครั้ง เพื่อเปิดคลิปบอร์ด

8) ตอนนี้เรามีสูตรที่เก็บไว้เพื่อใช้ในภายหลัง ดังนั้นเราจึงสามารถเคลียร์คอลัมน์ B และดำเนินการแยกนามสกุลได้

9) เรารู้ว่าช่องว่างอยู่ที่ไหน และเนื่องจากเราใช้ฟังก์ชัน LEFT เพื่อแยกชื่อ เราจึงสามารถใช้ฟังก์ชัน RIGHT เพื่อแยกนามสกุลได้ เรามาเริ่มสร้างสูตรสำหรับขั้นตอนการแยกนามสกุลกัน

10) เราจำเป็นต้องบอกฟังก์ชัน RIGHT ถึงจำนวนอักขระที่จะแยกจากด้านขวา เรารู้ตำแหน่งของพื้นที่ ดังนั้น เราจึงสามารถใช้ฟังก์ชัน LEN เพื่อบอกจำนวนอักขระทั้งหมดในสตริงข้อความ จากนั้นลบส่วนที่เหลือออกจากฟังก์ชัน SEARCH ซึ่งใช้เพื่อบอกเราในขั้นต้นว่าช่องว่างอยู่ที่ไหน

เราใช้สูตรต่อไปนี้:

=LEN (A5) – ค้นหา (” “, A5, 1))

สิ่งนี้ทำให้เรามีอักขระที่เหลือหลังจากเว้นวรรค

11) จากนั้นเราสามารถใช้ฟังก์ชัน RIGHT ร่วมกับสูตรด้านบนเพื่อแยกนามสกุล เนื่องจากตอนนี้เราทราบจำนวนอักขระจากด้านขวาเพื่อแยกเพื่อไปยังช่องว่าง

ดังนั้นเราจึงพิมพ์สูตรต่อไปนี้:

=ขวา (A5, LEN (A5) – ค้นหา (” “, A5, 1))

13) คัดลอกสูตรแล้วล้างเนื้อหาของเซลล์

14) ตอนนี้เราสามารถรวมทุกอย่างไว้ในสูตรเดียวได้ เนื่องจากเราได้แยกย่อยแต่ละขั้นตอนเพื่อแสดงตรรกะ และคัดลอกแต่ละส่วนไปยังคลิปบอร์ด

ดังนั้น หลังจากล้างคอลัมน์แล้ว ในเซลล์ B5 เราจะพิมพ์สูตร:

=ซ้าย (A5, ค้นหา (” “, A5, 1) – 1)&ขวา (A5, LEN(A5) – SEARCH(” “,A5,1))&”@mycompany.com”

15) เราดับเบิลคลิกแล้วส่งลงไป เพื่อเติมส่วนที่เหลือของคอลัมน์ด้วยที่อยู่อีเมล

คำแนะนำของผู้วิจารณ์ที่ยอดเยี่ยมเพื่อให้ได้ผลเช่นเดียวกัน:

ราหุล ซิงห์ สนับสนุนสูตรต่อไปนี้เพื่อให้ได้ผลเช่นเดียวกัน โดยใช้สูตรที่ซับซ้อนน้อยกว่ามาก:
ดังนั้นในเซลล์ B5 ให้พิมพ์สูตรต่อไปนี้:

=CONCATENATE(SUBSTITUTE(A5,” “, “”),”@mycompany.com”)

กาฬสินธุ์ สนับสนุนสูตรต่อไปนี้เพื่อให้ได้ผลเช่นเดียวกับสูตรข้อความเริ่มต้นในโพสต์ ซึ่งมีความซับซ้อนน้อยกว่ามาก:

=SUBSTITUTE(A5,” “,””)&”@mycompany.com”

อ่านเพิ่มเติม: วิธีใช้ Flash Fill ใน Excel เพื่อแยกข้อมูล (4 วิธีด่วน)

แฟ้มงาน

บทสรุป

Flash Fill เป็นคุณลักษณะใหม่ที่มีประโยชน์ใน Microsoft Excel 2013 และใหม่กว่า เป็นการป้อนอัตโนมัติที่ได้รับการปรับปรุงและสามารถประหยัดเวลาได้มาก อย่างไรก็ตาม สำหรับผู้ที่ใช้ Excel เวอร์ชันก่อนหน้าและในบางกรณีที่ Flash Fill ไม่ทำงาน สูตร TEXT จะช่วยได้ แม้ว่าการสร้างจะซับซ้อนกว่า แต่ก็ไม่มีตัวอย่างใดที่ซับซ้อนเกินกว่าจะรับมือไหว โปรดแสดงความคิดเห็นและแจ้งให้เราทราบหากคุณต้องการใช้ Flash Fill หรือสูตร TEXT เพื่อแยก/จัดรูปแบบข้อมูล พร้อมเหตุผล

บทความที่เกี่ยวข้อง

• วิธีปิด Flash Fill ใน Excel (2 วิธีง่ายๆ)
• [แก้ไขแล้ว!] เติมแฟลชไม่ทำงานใน Excel (5 เหตุผลพร้อมวิธีแก้ไข)
• Flash Fill ไม่รู้จักรูปแบบใน Excel (4 สาเหตุพร้อมการแก้ไข)