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:

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Sheepy1250

New Member
Joined
Jun 22, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Sorry for being so totally dim

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,642
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, it doesn't. If you read rule 13 here, and the associated link, it's all explained. :)
 

Sheepy1250

New Member
Joined
Jun 22, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Roger that Rory, thanks for the steer

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,642
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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))
 

Sheepy1250

New Member
Joined
Jun 22, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 22, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
You little Beauty - thanks Rory, that's brilliant 👏
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,642
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Sheepy1250

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

Watch MrExcel Video

Forum statistics

Threads
1,113,772
Messages
5,544,130
Members
410,594
Latest member
52779
Top