Hot Fox: Re: ]] XL-mania [[ Memisahkan angka, huruf, dan angka

domingo, 15 de agosto de 2010

Re: ]] XL-mania [[ Memisahkan angka, huruf, dan angka

Bagian :

y = Mid(x, i, 1) Like "#"

z = Mid(x, i + 1, 1) Like "#"

If (y = True And z = False) Or _

(y = False And z = True) Then

bisa disusun juga menjadi :
if mid(x,i,2) like "#[a-zA-Z]" or mid(x,i,2) like "[a-zA-Z]#" then

atau jika diubah sedikit :

Function Barry(x As Range)

'anton suryadi 15/Agt/2010

Dim i As Integer, y As String, z As String, s As String

For i = 1 To Len(x)

y = Mid(x, i, 1) Like "#"

z = Mid(x, i + 1, 1) Like "#"

If (y = True And z = False) Or _

(y = False And z = True) Then

s = s + Mid(x, i, 1) + " - "

Else: s = s + Mid(x, i, 1): End If

Next i

If Right(s, 3) = " - " Then

s = Left(s, Len(s) - 3): End If: Barry = s

End Function

menjadi :
Function Barry(x As string) as string
     Dim i As Integer, s As String
     s=left$(x,1)
     For i = 1 To Len(x)
          if mid(x,i,2) like "#[a-zA-Z]" or mid(x,i,2) like "[a-zA-Z]#" then s=s+" - "
          s=s+mid(x,i+1,1)
     Next i
     Barry = s
End Function

Regards.
Kid.

2010/8/15 anton suryadi <summonery@yahoo.com>

Coba juga,

Function Barry(x As Range)

'anton suryadi 15/Agt/2010
Dim i As Integer, y As String, z As String, s As String
For i = 1 To Len(x)

   y = Mid(x, i, 1) Like "#"
   z = Mid(x, i + 1, 1) Like "#"
   If (y = True And z = False) Or _
   (y = False And z = True) Then
   s = s + Mid(x, i, 1) + " - "
   Else: s = s + Mid(x, i, 1): End If

Next i
If Right(s, 3) = " - " Then
s = Left(s, Len(s) - 3): End If: Barry = s
End Function

File terlampir

>semoga bermanfaat

From: STDEV(i) <setiyowati.devi@gmail.com>
To: XL-mania@yahoogroups.com
Sent: Sat, August 14, 2010 4:59:37 PM
Subject: Re: ]] XL-mania [[ Memisahkan angka, huruf, dan angka

cukup ditulis rumus seperti ini

cara 1 (array formula) menghasilkan BEBERAPA CELL sebaris
ARRAY FORMULA ditulis sekaligus dlm beberapa cell sebaris
=SeparateNumberByText(C3)

cara2 (formula biasa) menghasilkan 1 cell berisi data yg sudah diberi tanda pemisah " - "
FORMULA biasa (ndak fake telor)
=PisahkanDong(C3)

'------------ listing code UDF / module 1-----------
Public Function SeparateNumberByText(Str As String)
' siti Vi / 14 aug 2010 / milis XL-maniak
' salah satu trick How To: membedakan
' SEKELOMPOK karakter yg ANGKA SEMUA -vs -
' SEKELOMPOK karakter yang HURUF SEMUA
' sedangkan kondisi data = semua karakter ngumpul jadi 1 string tanpa tanda pemisah

   Dim TxArr() As String, ka As String, kx As String
   Dim W As String, jka As String, jkx As String
   Dim i As Integer, j As Integer

   Str = Trim(Str) & " "
   For i = 1 To Len(Str)
      ka = Mid(Str, i, 1)
      If i > 1 Then kx = Mid(Str, i - 1, 1) Else kx = ka
      If InStr(1, "0123456789", ka) Then jka = "angka" Else jka = "huruf"
      If InStr(1, "0123456789", kx) Then jkx = "angka" Else jkx = "huruf"

      If jka = jkx Then
         W = W & ka
         If i = Len(Str) Then
            j = j + 1
            ReDim Preserve TxArr(1 To j)
            TxArr(j) = Trim(W)
         End If

      Else ' (IF jka <> jkx)
         j = j + 1
         ReDim Preserve TxArr(1 To j)
         TxArr(j) = Trim(W)
         W = ""
         W = W & ka
      End If
   Next i
   SeparateNumberByText = TxArr
End Function
'---- end of procedure --------

mungkin teman-temna lain ada yg punya algoritma yg lebih sederhana
misalnya mas Kid dan pak Anton itu sering punya cara yg 'Out of the Box'
tidak seperti di atas yg kuno & muter-muter seperti orang mabok keong racun,
sorry Jack,
-ctv-

2010/8/13 Barry <barry.allen@ymail.com>
>
> Dear Master XL,
> mau tanya soalnya ada kasus di kantor dengan contoh data
>
> 100SEV50
> 10SEV5
> SEV5
> 8TL100
> 6TL5
> dst..
> Agar menjadi
> 100SEV50 menjadi 100 - SEV - 50
> 6TL5 menjadi 6 - TL - 5
> dst..
> nach bagaimana caranya supaya memisahkan angka huruf kemudian angka langi
> pada kolom sebelahnya?? mohon bantuan para Master.
> Dan saya ucapkan terima kasih sebelumnya.

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (6)

Recent Activity:

New Members 90

Visit Your Group

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| selamat menunaikan ibadah puasa... walau perut lapar, tanya excel |
| jalan terus... malu bertanya telat pulang kantor :D :D :D         |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf          |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

MARKETPLACE

Hobbies & Activities Zone: Find others who share your passions! Explore new interests.

Get great advice about dogs and cats. Visit the Dog & Cat Answers Center.

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.