Tidying up and splitting data, a VBA newbie with a problem

[Sheepy1250]

Hi everyone

I'm hoping that someone with far more VBA knowledge than me can help with this one.

I've had some lovely assistance with the original issue but can't quite get to the final result I need.

I have some downloaded data that comes in the following format:

+++TOWN NAME (LLLNN) - where L=Letter and N=Number in a one-off code for each town

I am trying to split the data into adjacent columns so that it looks like this:

TOWN NAME - Column 2
LLLNN - Column 3

The VBA code I have been given by a lovely contributor on (sshhhhhh!) another forum has suggested this:

Option Explicit




Sub SplitData()
    Dim r1 As Range, r2 As Range, c As Range
    Dim s As String
    Dim v As Variant
    
    Set r1 = ActiveSheet.Range("A1")
    Set r2 = r1.End(xlDown)
    
    For Each c In Range(r1, r2).Cells
        
        'no spaces
        s = Trim(c.Value)
        's = "+++TOWN NAME 1 (LLLNN)"
        
        'leading plus's
        Do While Left(s, 1) = "+"
            s = Right(s, Len(s) - 1)
        Loop
        's = "TOWN NAME 1 (LLLNN)"
        
        'trailing )'s
        Do While Right(s, 1) = ")"
            s = Left(s, Len(s) - 1)
        Loop
        's = "TOWN NAME 1 (LLLNN"




        'start at 0,  i.e. v(0)
        v = Split(s, "(")
        'v(0) = "TOWN NAME 1"
        'v(1) = "LLLNN"


        '*************************************************
        'trailing numbers and spaces
        Do While Right(v(0), 1) = " " Or IsNumeric(Right(v(0), 1))
            v(0) = Left(v(0), Len(v(0)) - 1)
        Loop
        'v(0) = "TOWN NAME"
        'v(1) = "LLLNN"
        
        'put town one col over, and numbers 2 cols over
        c.Offset(0, 1).Value = Trim(v(0))
        c.Offset(0, 2).Value = Trim(Left(v(1), Len(v(1)) - 1))
    Next

End Sub

Which is fantastic but it misses off the final "N" in the column 3 results.

Now, call me Mr Stupid (I guess in VBA terms I am ) but somewhere in that lot is the reason the final number is being trimmed off the 5 character alphanumeric code for the town.

I am guessing this is a really simple fix for anyone with a decent enough working knowledge of VBA, a bunch of people I am definitely not a part of!

Can anyone help please?

Cheers

Mark - a total learner of VBA trying to understand its weirdness

 

[RoryA]

Cross-posted here: Total dummy when it comes to VBA, so hoping someone can help!

Please take a minute to read the forum rules on cross-posting and follow them in future. Thanks. Also, welcome to the forum!

 

[Sheepy1250]

Sorry for being so totally dim

Does that mean nobody can help if I have a partial solution from somewhere else then?

 

[RoryA]

No, it doesn't. If you read rule 13 here, and the associated link, it's all explained.

 

[Sheepy1250]

Roger that Rory, thanks for the steer

Question first asked HERE with response in first post above. Hope that works for you

 

[RoryA]

It's this line that removes the last character:

c.Offset(0, 2).Value = Trim(Left(v(1), Len(v(1)) - 1))

It sounds like you just want:

c.Offset(0, 2).Value = Trim(v(1))

​

 

[Sheepy1250]

Hi Rory

I suspected it might be, but am still feebly trying to get to grips with how VB hangs together (or not!)

I'll give it a whirl and let you know how it goes - thanks a million!

 

[Sheepy1250]

You little Beauty - thanks Rory, that's brilliant ?

 

[RoryA]

Glad to help.

 

[Sheepy1250]

Bu@@er! Just spotted one slight fly in the ointment. Some of the town names have additional brackets (for the county, where there is more than one town of the same name) - this throws the code out

So, they read like this in their raw state:

+++TOWN NAME (COUNTY) (LLLNN)

Why didn't I spot that before? The queries I created in the working sheet ignored those as they were stripped back from right to left, then the last unparenthesis removed before mapping across to the third column

So, the raw data appears in Column A as above
Column B does this - =RIGHT(A1,6)
Column C says - =RIGHT(A1,LEN(A1)-3)
Column D says - =LEFT(C1,LEN(C1)-7)
Column E does this - =LEFT(B1,5)

So effectively, these morph the original data into columns D & E to show the stripped down formats

I might stick to the original script lol