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 <[email protected]>
>
>
> 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) <[email protected]>
> *To:* [email protected]
> *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 <[email protected]>
> >
> > 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.
>
>
>
>
