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

Sheepy1250

New Member
Joined
Jun 22, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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 :)
 
Last edited by a moderator:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sorry for being so totally dim

Does that mean nobody can help if I have a partial solution from somewhere else then?
 
Upvote 0
No, it doesn't. If you read rule 13 here, and the associated link, it's all explained. :)
 
Upvote 0
Roger that Rory, thanks for the steer

Question first asked HERE with response in first post above. Hope that works for you :)
 
Upvote 0
It's this line that removes the last character:

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

It sounds like you just want:

Code:
c.Offset(0, 2).Value = Trim(v(1))
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top