Insert spaces into String

[Sandybam]

I have a data set where all of the spaces have been removed and no common
delimiter except for a capital letter for each new word. For example:
DependableHomeMedical

Is there a way to find upper case letters and insert a space in front of them
so the out put will look like: Dependable Home Medical?

Thank you for your help.
Sandy

 

[Guest]

You could use the REPLACE function inside a loop for all 26 letters e.g.
REPLACE(MyString,"A"," A").

Or, you could loop through each letter in the string and build a new string
inserting spaces where needed.

-Dorian

 

[Guest]

Sandy:

You can parse the string and insert a space before each upper case
character. First add this function to a standard module in the database:

Public Function InsertSpaces(varText As Variant) As Variant

Dim n As Integer, intTextlength As Integer
Dim strTemp As String, strChr As String

If Not IsNull(varText) Then
intTextlength = Len(varText)

strTemp = Left$(varText, 1)

For n = 2 To intTextlength
strChr = Mid$(varText, n, 1)
If Asc(strChr) = Asc(UCase(strChr)) Then
strTemp = strTemp & " " & strChr
Else
strTemp = strTemp & strChr
End If
Next n

InsertSpaces = strTemp
End If

End Function

The function works by walking through each character in the string and
comparing the ASCII value of it with the ASCII value of its upper case
character. If the values are the same the character is in upper case so a
space is inserted before it. You can call it in an update query to update
the values of the fields by inserting spaces, e.g.

UPDATE MyTable
SET Field1 = InsertSpaces(Field1),
Field2 = InsertSpaces(Field2);

If you data includes people watch out for any names like MacDonald as this
will become Mac Donald (as its my wife's name she would not be amused!).
Also some names use lower case e.g. the Spanish soprano Victoria de los
Angeles who would become Victoriadelos Angeles. And you should always back
up your table before undertaking any large scale update operation like this.

Ken Sheridan
Stafford, England

 

[fredg]

I have a data set where all of the spaces have been removed and no common
delimiter except for a capital letter for each new word. For example:
DependableHomeMedical

Is there a way to find upper case letters and insert a space in front of them
so the out put will look like: Dependable Home Medical?

Thank you for your help.
Sandy

Why would you first remove the spaces and now want to add them back?

Place the following User Defined function in a new Module:

Function PlaceSpaces(FieldIn As String) As String

Dim strNew As String
Dim intX As Integer
Dim intY As Integer

strNew = Left(FieldIn, 1)

For intX = 2 To Len(FieldIn)
intY = Asc(Mid(FieldIn, intX, 1))
If intY >= 65 And intY <= 90 Then
strNew = strNew & Chr(32) & Chr(intY)
Else
strNew = strNew & Chr(intY)
End If
Next intX
PlaceSpaces = strNew

End Function
=========

In a query, add a new column:
NewNamelaceSpaces([OriginalFieldName])
as criteria on the OriginalFieldName field, write:
Is Not Null

By the way, what do you want to do about names such as
McDonaldBusinessCenter or ABCHomeSupplies, O'BrienMedicalCenter, and
vanderMeerAndSonsSuppyCo.?

 

[Sandybam via AccessMonster.com]

Ken,

Thank you for your help. I have a relatively small data set so I can eye
ball the output for issues. Also, I am working with medical company names,
which are usually pretty generic. I really appreciate your clear
instructions as I am learning by fire.

Sandy

Sandy:

You can parse the string and insert a space before each upper case
character. First add this function to a standard module in the database:

Public Function InsertSpaces(varText As Variant) As Variant

Dim n As Integer, intTextlength As Integer
Dim strTemp As String, strChr As String

If Not IsNull(varText) Then
intTextlength = Len(varText)

strTemp = Left$(varText, 1)

For n = 2 To intTextlength
strChr = Mid$(varText, n, 1)
If Asc(strChr) = Asc(UCase(strChr)) Then
strTemp = strTemp & " " & strChr
Else
strTemp = strTemp & strChr
End If
Next n

InsertSpaces = strTemp
End If

End Function

The function works by walking through each character in the string and
comparing the ASCII value of it with the ASCII value of its upper case
character. If the values are the same the character is in upper case so a
space is inserted before it. You can call it in an update query to update
the values of the fields by inserting spaces, e.g.

UPDATE MyTable
SET Field1 = InsertSpaces(Field1),
Field2 = InsertSpaces(Field2);

If you data includes people watch out for any names like MacDonald as this
will become Mac Donald (as its my wife's name she would not be amused!).
Also some names use lower case e.g. the Spanish soprano Victoria de los
Angeles who would become Victoriadelos Angeles. And you should always back
up your table before undertaking any large scale update operation like this.

Ken Sheridan
Stafford, England

I have a data set where all of the spaces have been removed and no common
delimiter except for a capital letter for each new word. For example:

[quoted text clipped - 5 lines]

Thank you for your help.
Sandy

 

[Sandybam via AccessMonster.com]

Fred,

Thank you for your feedback. As I indicated to Ken, I am learning code as
problems arise and as I inherit bad data as I did with this. I appreciate
your clear step-by-step instructions.

Sandy

I have a data set where all of the spaces have been removed and no common
delimiter except for a capital letter for each new word. For example:

[quoted text clipped - 5 lines]

Thank you for your help.
Sandy

Why would you first remove the spaces and now want to add them back?

Place the following User Defined function in a new Module:

Function PlaceSpaces(FieldIn As String) As String

Dim strNew As String
Dim intX As Integer
Dim intY As Integer

strNew = Left(FieldIn, 1)

For intX = 2 To Len(FieldIn)
intY = Asc(Mid(FieldIn, intX, 1))
If intY >= 65 And intY <= 90 Then
strNew = strNew & Chr(32) & Chr(intY)
Else
strNew = strNew & Chr(intY)
End If
Next intX
PlaceSpaces = strNew

End Function
=========

In a query, add a new column:
NewNamelaceSpaces([OriginalFieldName])
as criteria on the OriginalFieldName field, write:
Is Not Null

By the way, what do you want to do about names such as
McDonaldBusinessCenter or ABCHomeSupplies, O'BrienMedicalCenter, and
vanderMeerAndSonsSuppyCo.?