การลบ Text Format

ในการนำข้อมูลจากโปรแกรมอื่นเช่น SQL, Oracle, หรือจาก Web เข้ามายัง Excel ในบางครั้งพบว่าจะมีเครื่องหมาย ‘ (เครื่องหมายฝนทอง) ติดมาหน้าตัวอักษรและตัวเลขด้วย เครื่องหมายฝนทองที่อยู่ด้านหน้าสุดนำหน้าอักขระใด ๆ ในเซลล์เช่นนี้ใน Excel ถือว่าเป็นการจัดรูปแบบข้อมูลให้เป็น Text ครับ ซึ่งเป็นการจัดรูปแบบลักษณะหนึ่ง

กรณีที่เครื่องหมายฝนทองนำหน้าตัวเลขซึ่งมีรูปแบบเป็น Text แต่เราต้องการเปลี่ยนแปลงให้เป็น Number โดยให้ตัวเลขชิดขวาเซลล์ในทันที เราสามารถ Copy เซลล์ว่างที่ไม่มีค่าใด ๆ มา Paste Special > Value > Add ได้ ซึ่งหมายถึงการบวกด้วย 0 นั่นเองครับ

แต่กรณีที่ข้อมูลเดิมเป็นข้อความและมีเครื่องหมายฝนทองนำหน้า (ซึ่งไม่จำเป็นเพราะไม่มีเครื่องหมายฝนทองนำหน้าก็เป็นข้อความอยู่แล้ว) เราสามารถที่จะปลดเครื่องหมายฝนทองไปเสียก็ได้ครับ โดย

1. คลุมคอลัมน์ที่ไม่มีการจัดรูปแบบใด ๆ
2. คลิกขวา > Copy
3. คลุมข้อมูลปลายทาง > คลิกขวา > Paste Special
4. เลือก Format > OK

ดูรูปประกอบตามด้านล่าง



สามารถถาม-ตอบปัญหา Excel ได้ที่ http://www.snasui.com/ ซึ่งสามารถแนบไฟล์แนบภาพได้ครับ

การเปลี่ยนตัวเลข 1, 2, 3, …0 ให้เป็น A, B, C, …J

การเปลี่ยนข้อความลักษณะนี้สามารถใช้สูตร Substitute มาช่วยได้ครับ แต่หากตัวที่ต้องการเปลี่ยนมีจำนวนมากใน Excel 2003 จะต้องพึ่งเซลล์ช่วยหลายเซลล์เนื่องจากสามารถซ้อนสูตรได้เพียง 7 ชั้น แต่หากเป็น Excel 2007 ขึ้นไป สามารถที่จะซ้อนสูตรได้ถึง 64 ชั้นจึงสามารถเขียนสูตรในเซลล์เดียวกรณีไม่เกิน 64 เงื่อนไข

ภาพประกอบ



ตัวอย่างการใช้สูตรใน Excel 2003

สมมุติค่าเป้าหมายเริ่มที่ A1

1. ที่ B1 คีย์สูตรในลำดับที่ 1
   =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,"J"),1,"A"),2,"B"),3,"C"),4,"D"),5,"E"),6,"F"),7,"G")
   Enter
2. ที่ C1 คีย์สูตรในลำดับที่ 2 เพื่อเปลี่ยนให้เป็นค่าที่ต้องการ
   =SUBSTITUTE(SUBSTITUTE(B1,8,"H"),9,"I")
   Enter

ตัวอย่างการใช้สูตรใน Excel 2007

ที่ B1 คีย์
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,"J"),1,"A"),2,"B"),3,"C"),4,"D"),5,"E"),6,"F"),7,"G"),8,"H"),9,"I")
Enter

สำหรับท่านที่คุ้นเคยกับการใช้งาน VBA สามารถเขียนฟังก์ชั่นในการเปลี่ยนค่าตามลักษณะสูตรด้านบนได้ดังนี้ครับ

Option Explicit 
Function ctext(r) As String 
    Dim i As Byte 
    For i = 1 To Len(r) 
        Select Case Mid(r, i, 1) 
            Case 1: Mid(r, i, 1) = "A" 
            Case 2: Mid(r, i, 1) = "B" 
            Case 3: Mid(r, i, 1) = "C" 
            Case 4: Mid(r, i, 1) = "D" 
            Case 5: Mid(r, i, 1) = "E" 
            Case 6: Mid(r, i, 1) = "F" 
            Case 7: Mid(r, i, 1) = "G" 
            Case 8: Mid(r, i, 1) = "H" 
            Case 9: Mid(r, i, 1) = "I" 
            Case 0: Mid(r, i, 1) = "J" 
        End Select 
    Next i 
    ctext = r 
End Function

และหากมีเงื่อนไขว่าให้ลบค่าอักขระที่ไม่ใช่ Number ออกทั้งหมด ส่วนค่าที่เป็น Number ให้เปลี่ยนเป็นอักษรตามเงื่อนไขด้านบน สามารถใช้ Code ตามด้านล่างได้ครับ

Function cntt(r) As String 
    Dim i As Byte 
    Dim b As Variant 
    b = Array("J", "A", "B", "C", "D", "E", "F", "G", "H","I") 
    For i = 1 To Len(r) 
        On Error Resume Next 
        cntt = cntt & b(Mid(r, i, 1)) 
    Next i 
End Function