Seprate data with space after every 3 chars

[ilyaskazi]

if my ActiveCell data= "ATLBOSFLLMIAMEX"
then Output data= "ATL BOS FLL MIA MEX"

if my ActiveCell data= "ATLBOSFLLMIAMEXI" (one char extra)
then Output= msgbox "invalid data"

if my ActiveCell data= "ATLBOSFLLMIAME" (one char less)
then Output= msgbox "invalid data"

There can be 2 char extra or 2 char less in above activecell data.

activecell data should always 3+3 chars. if not then: "invalid data"

[doctordoggie]

Here's one way to check if a string/s length is a multiple of 3:

The formula:
(Len("yourstring") Mod 3)
will return 0 if the length of yourstring divides by 3, else it will return 1 or 2 depending on how many extra characters there are in there.

This will do the other job:


Sub test()
Dim myinput, myoutput As String
myoutput = ""
myinput = "abcdefghi"
For i = 1 To Len(myinput) Step 3
myoutput = myoutput + Mid(myinput, i, 3) + " "
Next i
MsgBox (myoutput)
End Sub

[Bob Phillips]

if my ActiveCell data= "ATLBOSFLLMIAMEX"
then Output data= "ATL BOS FLL MIA MEX"

if my ActiveCell data= "ATLBOSFLLMIAMEXI" (one char extra)
then Output= msgbox "invalid data"

if my ActiveCell data= "ATLBOSFLLMIAME" (one char less)
then Output= msgbox "invalid data"

There can be 2 char extra or 2 char less in above activecell data.

activecell data should always 3+3 chars. if not then: "invalid data"


Dim nSets As Long
Dim stemp As String
Dim i As Long


With ActiveCell
nSets = Len(.Value) Mod 3
If nSets = 0 Then
For i = Len(.Value) To 3 Step -3
stemp = Mid(.Value, i - 2, 3) & " " & stemp
Next i
stemp = Left(stemp, Len(stemp) - 1)
Else
MsgBox "Invalid data"
End If
.Value = stemp
End With