How to determine if a space is needed in Street Name or City?

Question

Problem: I would like to be able to determine if a space is needed in a Street Name or City Name.

The reason being is that users have the habit to combine two words into one.

Ex. In the database, "Bell Gardens" is entered as "BellGardens".

Is their a way to determine when a user does that and add the space accordingly?

@GordonLinoff: Okay, that is what I figured. I know in excel, their is a way to differentiate numbers with characters using things like LTR and so on. I thought if their was a way to do that with characters. Thank you for your input — Robert, Oct 05 '17 at 23:20
Do you have a list of all the Street Names and Cities? Maybe use a dropdown instead? Or an autocomplete? — Marcelo, Oct 05 '17 at 23:31
You could potentially check every entry in your "valid" list testing whether the entry (after removing spaces from it) matches the user input (after removing spaces from it). — YowE3K, Oct 06 '17 at 00:28
You might examine the capitalisation and add a space before an upper case letter which doesn't have one. Of course, that would leave you with Mc Donald instead of McDonald and O' Brien instead of O'Brien, but you might program exceptions for when a space must not be inserted before an upper case letter. — Variatus, Oct 06 '17 at 02:33

danieltakeshi · Answer 1 · 2017-10-06T12:03:05.293

You can use Regex, here is a Regex validation test.

And below the explanation of how to do it.

The code and Regex were updated, go to the end to check them.

Enable Regex on Excel

Open Excel workbook.
Alt+F11 to open VBA/Macros window.
Add reference to regex under Tools then References
and selecting Microsoft VBScript Regular Expression 5.5
Insert a new module (code needs to reside in the module otherwise it doesn't work).
In the newly inserted module,

Code

add the following code:

Sub test_regex()

Dim str As String
Dim objMatches As Object
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)
Dim i As Long

lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'To check the name
For i = 1 To lastrow
    str = Trim(CStr(Cells(i, 1)))
    Set objRegExp = CreateObject("VBScript.RegExp") 'New regexp
    objRegExp.Pattern = "^(?![ ])(?!.*[ ]{1})(?:([A-Z][a-z]+\s*?)([Mc|O']*[A-Z][a-z]+\s*?)*(?!.*[ ])$)+$"
    objRegExp.Global = True
    Set objMatches = objRegExp.Execute(str)
    If objMatches.Count <> 0 Then
            ws.Cells(i, 2) = objRegExp.Replace(str, "$1" & " " & "$2")
            ws.Cells(i, 2).Interior.ColorIndex = 4
    Else
        ws.Cells(i, 2).Interior.ColorIndex = 3
        ws.Cells(i, 2) = ws.Cells(i, 1)
    End If
Next i

End Sub

Result

This code will Trim the name and add whitespaces according to Regex. Only for 2 Uppercase names, except for Mc and O'

If the name is like ThIsNaMe, the output will be Th Me

The code color in red names that are already right and in green the ones that have some spacing problems.

Ps.: Since i am new to Regex, there is space for optimization.

Update Edit:

For a more complex Regex and for more than 2 Uppercases case and with numbers, here is the Regex Update

Code

The color index was Swapped, so when the color is red, means that it was changed. So the user can verify visually the changes.

Sub test_regex()

Dim str As String, final_str As String
Dim objMatches As Object
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)
Dim i As Long, k As Long, lastrow As Long

lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'To check the name
For i = 1 To lastrow
    str = Trim(CStr(Cells(i, 1)))
    Set objRegExp = CreateObject("VBScript.RegExp") 'New regexp
    objRegExp.Pattern = "(?!.*[ ]{1})(?:(?:[Mc|O']*[A-Z]{1}[a-z]+)|(?:[\d]+[rd|th|st|nd]*))"
    objRegExp.Global = True
    Set objMatches = objRegExp.Execute(str)
    If objMatches.Count > 1 Then
          final_str = ""
          For Each m In objMatches
            final_str = final_str & " " & CStr(m.Value)
          Next
            ws.Cells(i, 2).Interior.ColorIndex = 3
            ws.Cells(i, 2) = Trim(final_str)
    Else
        ws.Cells(i, 2).Interior.ColorIndex = 4
        ws.Cells(i, 2) = ws.Cells(i, 1)
    End If
Next i

End Sub

Result

score 0 · Answer 2 · answered Oct 05 '17 at 23:34

0

you cant do that, you need some sort of pattern in order to automate that, but in your case it is not possible to automate the 'space' just by using street name or city name.

answered Oct 05 '17 at 23:34

NT-Hero

77
11

How to determine if a space is needed in Street Name or City?

2 Answers2

Enable Regex on Excel

Code

Result

Update Edit:

Code

Result