RSS

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

03 Apr

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

ภาพประกอบ

ChangeNumToText

ตัวอย่างการใช้สูตรใน 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

Smile สำหรับท่านที่คุ้นเคยกับการใช้งาน 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

Winking smile และหากมีเงื่อนไขว่าให้ลบค่าอักขระที่ไม่ใช่ 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

 
Leave a comment

Posted by on 03/04/2010 in Substitute

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: