add a space between characters / numbers

  • Thread starter Thread starter mike UH
  • Start date Start date
M

mike UH

I have a field called Customer Number with Text / alpha numeric data
type, which varies in length. i need to insert a space between the
characters.

example
1234 becomes 1 2 3 4
asdf123 becomes a s d f 1 2 3

please help me on how to solve this problem.

Thanks
Mike
 
One way to do this would be to use a Custom VBA function.
Untested -- use at your own risk

Public Function fAddSpaces(strIN)
Dim strOut As String
Dim i As Long

If Len(Trim(strIN & "")) = 0 Then
fAddSpaces = strIN
Else
For i = 1 To Len(strIN)
strOut = strOut & Mid(strIN, i, 1) & Space(1)
Next i
'Add next 3 lines if you want to get rid of multiple spaces in a row
While strOut Like "* *"
strOut = Replace(strOut, " ", " ")
Wend
'Add next line if you want to trim leading or trailing spaces
strOut = Trim(strOut)

fAddSpaces = strOut
End If

As written the results should be:
=fAddSpaces(" ABC DEF ") returns "A B C D E F"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I have a field called Customer Number with Text / alpha numeric data
type, which varies in length. i need to insert a space between the
characters.

example
1234 becomes 1 2 3 4
asdf123 becomes a s d f 1 2 3

please help me on how to solve this problem.

Thanks
Mike
Click to expand...

Create a User Defined function in a module:

Public Function AddSpaces(DataIn As String) As String
Dim intX As Integer
For intX = 1 To Len(DataIn)
AddSpaces = AddSpaces & Mid(DataIn, intX, 1) & " "
Next intX
End Function

Add Error Handling as needed.

Call it from the AfterUpdate event of the control on your form:

[ThisControlName] = AddSpaces([ThisControlName])
 
Sounds weird. Why do you need to do that? Is this for a report?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Back
Top