Add spaces after every 6th character (1 Viewer)

ajb_1976

Registered User.
Local time
Today, 14:00
Joined
Feb 25, 2005
Messages
34
Hi, is there a way I can use an update query or piece of VBA code to automatically add a space after every 6th character in a text string. This string will vary in length from 6 to 36 chars. Thanks
 

Ranman256

Well-known member
Local time
Today, 10:00
Joined
Apr 9, 2015
Messages
4,339
paste this into a module, then you can use it in a query or a form:
query usage: Add6([field])

Code:
Public Function Add6(ByVal pvWord)
Dim i As Byte, j As Byte
Dim vNew, vChr

j = 1
For i = 1 To Len(pvWord)
   vChr = Mid(pvWord, i, 1)
   If j = 7 Then
      vNew = vNew & " "
      j = 1
   End If
   vNew = vNew & vChr
   j = j + 1
Next
Add6 = vNew
End Function
 

ajb_1976

Registered User.
Local time
Today, 14:00
Joined
Feb 25, 2005
Messages
34
that is absolutely perfect! thanks so much
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:00
Joined
Jan 20, 2009
Messages
12,849
that is absolutely perfect! thanks so much

Workable, yes. Perfect? No.

Code:
Public Function Add6(ByVal pvWord)
Dim i As Byte, j As Byte
Dim vNew, vChr

j = 1
For i = 1 To Len(pvWord)
   vChr = Mid(pvWord, i, 1)
   If j = 7 Then
      vNew = vNew & " "
      j = 1
   End If
   vNew = vNew & vChr
   j = j + 1
Next
Add6 = vNew
End Function
Firstly it passes a Variant. Passing a variant might look like a good way to handle Null parameter but this function would fail anyway because Mid() cannot handle a Null. Functions designed to process strings should specify a string argument and a test applied in the call to avoid Nulls if necessary under the circumstances.

It is also good practice to specify ByVal to arguments so that it is clear that changes to the parameters are not being passed back to the caller.

The function wastes a lot of time reading and concatenating every individual character when it could simply read groups of six. The following function avoids these shortcomings and add two extra features just for fun.

Code:
Public Function InsertSpace(ByVal psWord As String, ByVal GroupChars As Integer, Optional ByVal NumSpaces As Integer = 1) As String
   
Dim i As Integer
Dim sChr As String
Dim sNew As String

    i = 1
    
    Do
        sChr = Mid(psWord, i, GroupChars)
        
        If Len(sChr) = 0 Then Exit Do
        sNew = sNew & sChr & Space(NumSpaces)
        i = i + GroupChars
    Loop
        
       InsertSpace = RTrim(sNew)

End Function
 

isladogs

MVP / VIP
Local time
Today, 14:00
Joined
Jan 14, 2017
Messages
18,186
OK I'll bite.

It's a very neat solution and far more elegant than the half complete version that I've just scrapped.

However, it could I think be generalised further by adding a further optional argument for the character to be added and setting a space as the default.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:00
Joined
May 7, 2009
Messages
19,169
simple Update query will do:

Update table1
Set fld=Iif(Len(Trim(fld & ""))<6, fld, Left(fld, 5) & " " & Mid( fld, 6))
 

isladogs

MVP / VIP
Local time
Today, 14:00
Joined
Jan 14, 2017
Messages
18,186
simple Update query will do:

Update table1
Set fld=Iif(Len(Trim(fld & ""))<6, fld, Left(fld, 5) & " " & Mid( fld, 6))

Sorry Arnelgp, but that's wrong for 2 reasons:
-should add space after EVERY SIXTH character

Here's a query testing Galaxiom's CORRECT solution with yours:

Code:
SELECT tblTest.TestString, InsertSpace([testString],6,1) AS Galaxiom, IIf(Len(Trim([TestString] & ""))<6,[TestString],Left([TestString],5) & " " & Mid([TestString],6)) AS Arnelgp
FROM tblTest;

Results:


Ranman's solution gives the correct results as well
 

Attachments

  • Capture.PNG
    Capture.PNG
    8.7 KB · Views: 143

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:00
Joined
May 7, 2009
Messages
19,169
Now this is corrwxt without vba:

simple Update query will do:

Update table1
Set fld=Iif(Len(Trim(fld & ""))<7, fld, Left(fld, 6) & " " & Mid( fld, 7))
 

isladogs

MVP / VIP
Local time
Today, 14:00
Joined
Jan 14, 2017
Messages
18,186
How it is wrong? When you add space on the 6 char the 6 char will be the space and the 6 char and the rest rest of text will shift to 7..

Read the title: Add spaces after every 6th character

You can easily change your 5 to a 6 but the loop is still necessary

However, your method does at least handle nulls which the other methods don't .... unless also enclosed in Nz(...,"")
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:00
Joined
Jan 20, 2009
Messages
12,849
it could I think be generalised further by adding a further optional argument for the character to be added and setting a space as the default.

Yes I thought that too. Would be good for dashes in phone numbers.

This made me realise it is really a formatting task and wondered how ajb was using it in their app.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:00
Joined
Jan 20, 2009
Messages
12,849
However, your method does at least handle nulls which the other methods don't .... unless also enclosed in Nz(...,"")

Expressions not using custom functions should always be preferred.

BTW it is a general practice to not pass variants to functions. Look at all the string functions in VBA. It is a lot more efficient to pass a scalar variable because a Variant is a much more expensive structure.

The Null is better tested before calling the function because an IS NULL expression can be used, employing the native capability of the engine. The call of the function is saved entirely and that can amount to a lot of time especially if the data is sparse.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:00
Joined
May 21, 2018
Messages
8,463
Expressions not using custom functions should always be preferred.
I think that always needs a few caveats.
In line expressions are usually faster, (but that depends on the limitation of what can be done by an in line expression).
Expression Pros:
1. Expressions are most often faster so preferred if running a large query
2. Expressions are more portable to an upgrade to SQL server

But in a pure Access environment (never upgrading) and smaller data sets, a UDF used in a query is often a much better solution.
UDF Pros
1. Far more Reusable
2. Far Easier to debug
3. Far easier to error check
4. Far more readable for any complex function
5. Can be faster if it overcomes limitations of an inline expression
6. Can be far easier to write (my favorite are when people try the nested iff with 10 cases and cannot get it to work or even read it.)
 

isladogs

MVP / VIP
Local time
Today, 14:00
Joined
Jan 14, 2017
Messages
18,186
Yes I thought that too. Would be good for dashes in phone numbers.

This made me realise it is really a formatting task and wondered how ajb was using it in their app.

However, with phone numbers, the number blocks aren't usually the same lengths e.g. 01234 567890 ; 333-456-4562

Good point re formatting.
Could use something like this as an input mask: >000000\-000000\-000000\-000000
 

Users who are viewing this thread

Top Bottom