Custom VBA Function

[helpexce]

Hello experts,

Can you help me with VBA code for custom function to separate abcd1234efg12 into abcd 1234 efg 12 i.e. for every change from letter to numeric and viceversa adding space in between.

Thanks.

 

[MickG]

Try this:-

Function nSpace(Txt [COLOR=navy]As[/COLOR] String) [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]For[/COLOR] n = 1 To Len(Txt)
    [COLOR=navy]If[/COLOR] Mid(Txt, n, 1) Like "[0-9]" And Mid(Txt, n + 1, 1) Like "[a-z]" [COLOR=navy]Then[/COLOR]
        nStr = nStr & Mid(Txt, n, 1) & " "
    [COLOR=navy]ElseIf[/COLOR] UCase(Mid(Txt, n, 1)) Like "[A-Z]" And Mid(Txt, n + 1, 1) Like "[0-9]" [COLOR=navy]Then[/COLOR]
        nStr = nStr & Mid(Txt, n, 1) & " "
    [COLOR=navy]Else[/COLOR]
        nStr = nStr & Mid(Txt, n, 1)
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
nSpace = nStr
[COLOR=navy]End[/COLOR] Function

Regards Mick

 

[mikerickson]

Perhaps

Function AddSpaces(aWord As String) As String
    Dim i As Long
    Dim flag As Boolean, aLetter As String
    flag = Left(aWord, 1) Like "#"
    
    For i = 1 To Len(aWord)
        aLetter = Mid(aWord, i, 1)

        If flag Xor (aLetter Like "#") Then
            AddSpaces = AddSpaces & " "
        End If
        AddSpaces = AddSpaces & aLetter

        flag = aLetter Like "#"
    Next i
End Function

 

[helpexce]

Great! Thank you MickG and mikerickson.

 

[helpexce]

Perhaps

Function AddSpaces(aWord As String) As String
    Dim i As Long
    Dim flag As Boolean, aLetter As String
    flag = Left(aWord, 1) Like "#"
    
    For i = 1 To Len(aWord)
        aLetter = Mid(aWord, i, 1)

       [COLOR=#ff0000] If flag Xor (aLetter Like "#")[/COLOR] Then
            AddSpaces = AddSpaces & " "
        End If
        AddSpaces = AddSpaces & aLetter

        flag = aLetter Like "#"
    Next i
End Function

Can you please explain me the command in the colored text?
Thank you.

 

[mikerickson]

Flag is a variable that indicates if the previous character was a numeral (0-9).

(aLetter Like "#") indicates if the current character is a numeral.

Xor (exclusive Or) is true if only one of the arguments is true, not both.

So if the previous character is a numeral and the current character is not a numeral, insert a space.
If the previous character is not a numeral and the current character is a numeral, insert a space.
Otherwise, don't.

 

[Rick Rothstein]

Here is another function that you can consider...

[table="width: 500"]
[tr]
	[td]Function AddSpaces(ByVal S As String) As String
  Dim X As Long
  For X = Len(S) - 1 To 1 Step -1
    If Mid(S, X, 2) Like "[A-Za-z]#" Or Mid(S, X, 2) Like "#[A-Za-z]" Then S = Application.Replace(S, X + 1, 0, " ")
  Next
  AddSpaces = S
End Function[/td]
[/tr]
[/table]

 

[helpexce]

Flag is a variable that indicates if the previous character was a numeral (0-9).

(aLetter Like "#") indicates if the current character is a numeral.

Xor (exclusive Or) is true if only one of the arguments is true, not both.

So if the previous character is a numeral and the current character is not a numeral, insert a space.
If the previous character is not a numeral and the current character is a numeral, insert a space.
Otherwise, don't.

Thank you mikerickson. I got confused with the flag variable. But it seems i am clear now. Thanks a lot.

 

[helpexce]

Thank you Rick. It also worked perfectly. I am not familiar with 'like' function and '#' wild card character in vba so it took me some time to understand the code. Will get around it soon. Thank you all.