• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insert space before Uppercase, except if preceded by space or Uppercase

Chris Scarpulla

New Member
Hey fellow gurus!

Hoping to get a hand with the following:

I'm using Power Query to pull game data from a website, and there are instances where I need to insert a space before a capital letter, but ONLY if the preceding letter is NOT a capital letter.

I found a few VBA solutions that get me halfway there, but they all insert a space before each and every capital, which just doesn't get me where I need to be.

Here are a few examples from the data.

Before CloudmusiqIntercontinental Cup
After Cloudmusiq Intercontinental Cup

Before Maplethorpe TyresPre-Season Euro Cup
After Maplethorpe Tyres Pre-Season Euro Cup

Before Aston MartinDB9Showcase
After Aston Martin DB9 Showcase

Before SupaflySpeed Test
After Supafly Speed Test

This one is a bit different.
Before Circuit des 24 Heures(Night)
After Circuit des 24 Heures (Night)


The source data is here: http://rr3.wikia.com/wiki/Luxury_Tourer_Tournament

Thanks in advance!
Chris
 

Attachments

  • RR3_PowerQuery_Example.xlsx
    21.2 KB · Views: 8

Hi !

When I use a single QueryTable (Data menu, from Web …) to import
this table, all texts are correct !
 
Hey Marc,

When I use QueryTable, it breaks each event into multiple rows (sometimes two, and sometimes three), which is a step in the wrong direction, unless you can show me a simple way around that.

My goal is to make this as simple as possible, because there are dozens of these tables.

Before I discovered PowerQuery, I was manually highlighting the data, and pasting it into Excel, which as you can imagine, is pretty laborious.

Either way, thanks for the reply!

Chris
 
Before CloudmusiqIntercontinental Cup
After Cloudmusiq Intercontinental Cup

Before Maplethorpe TyresPre-Season Euro Cup
After Maplethorpe Tyres Pre-Season Euro Cup

Before Aston MartinDB9Showcase
After Aston Martin DB9 Showcase

Before SupaflySpeed Test
After Supafly Speed Test

This one is a bit different.
Before Circuit des 24 Heures(Night)
After Circuit des 24 Heures (Night)

Chris
Try this
Code:
Sub test()
    Dim a, i As Long
    With Range("b2", Range("b" & Rows.Count).End(xlUp))
        a = .Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "([^-])(\(?([A-Z]+\d*|[A-Z][a-z]+))"
            For i = 1 To UBound(a, 1)
                a(i, 1) = Application.Trim(.Replace(a(i, 1), "$1 $2"))
            Next
        End With
        .Value = a
    End With
End Sub
 
@jindon , you're a life saver!

It's working brilliantly, thank you so much.

These are the only entries which still need work:
Aston Martin DB9Showcase
Aston Martin DB9Time Trial
Speedrush TVEastern Exhibition
Speedrush TVLuxury Tourer Time Trial
Mastare USSpeedway Demonstration​

Is it possible to add explicit alphanumeric combinations that would have a space added after them? Where I'm going with that would be to add DB9 TV and US to the formula so that a space is added after them if one does not already exist.

I expect I'll run across similar issues on other pages, with other alphanumeric combinations which 'special' handling like TV , US , etc.

Thanks again!
Chris
 
Try this
Code:
Sub test()
    Dim a, i As Long, m As Object
    With Range("b2", Range("b" & Rows.Count).End(xlUp))
        a = .Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            For i = 1 To UBound(a, 1)
                .Pattern = "(\(?([A-Z][a-z]+|[A-Z]+(?![a-z])))"
                a(i, 1) = Application.Trim(.Replace(a(i, 1), " $1"))
                .Pattern = "(-) +"
                a(i, 1) = .Replace(a(i, 1), "$1")
            Next
        End With
        .Value = a
    End With
End Sub
 
It adds:
1) Before upper case alphabet followed by small case alphabets.
2) Before consecutive upper case alphabets besides the one with lower case alphabets.
e.g
USSpeedway
will be separated US and Speedway
USS : 3 consecutive upper case but not with the last S, because it has small case Speedway
3) delete space after the hyphen. (might be added when Upper case is followed)

Any question?
 
Back
Top