Total dummy when it comes to VBA, so hoping someone can help!

[Sheepy1250]

Hi everyone

Really not sure how to do this in VBA or with a Macro, but here goes:

I have a column of information that I currently split out using a variety of queries in a worksheet (Office 365 version)

The basic format is like this (see attached example)

+++TOWN NAME (LLLNN) - [where L=a letter and N = a number]

These sit in column A

I need to show these in a new format in two separate columns, thus:
Column 2 - TOWN NAME
Column 3 - LLLNN

Currently using the queries as follows:
=RIGHT(A1,6) - to extract the final 6 characters which brings up LLLNN) in the result
Then, in the next column:
=RIGHT(A1,LEN(A1)-3) to remove the +++ from the cell data
...and then
=LEFT(C1,LEN(C1)-7) to remove everything but the town name from the data
and finally...
=LEFT(B1,5) to show the LLLNN section I need

It works fine, but I'm hoping to create a piece of code that will perform these functions down a variable length list in column A until it meets the first blank cell.

Any suggestions very, very welcome - am trying to wrap my head around VBA but it seems to be meeting a bit of a block (my somewhat dim brain!)

[Sheepy1250]

[snb]

But what if you do not understand the help we offer ?

[Sheepy1250]

But what if you do not understand the help we offer ?

It's all part of the great big learning curve I'm on right now!

Prepared to listen to any and all advice/guidance

[BIFanatic]

Does this work for you?

Sub Test()    

    Dim LastRow As Long
    
    With Sheet1
    
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            
        .Range("G1:G" & LastRow).Formula = "=IF(A1="""","""",RIGHT(A1,6))"
        .Range("H1:H" & LastRow).Formula = "=IF(A1="""","""",RIGHT(A1,LEN(A1)-3))"
        .Range("I1:I" & LastRow).Formula = "=IF(A1="""","""",LEFT(H1,LEN(H1)-7))"
        .Range("J1:J" & LastRow).Formula = "=IF(A1="""","""",LEFT(G1,5))"
        
    End With
    
End Sub

[Paul_Hossler]

Or 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)
        
        'leading plus's
        Do While Left(s, 1) = "+"
            s = Right(s, Len(s) - 1)
        Loop
        
        'trailing )'s
        Do While Right(s, 1) = ")"
            s = Left(s, Len(s) - 1)
        Loop


        'start at 0,  i.e. v(0)
        v = Split(s, "(")
        
        '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

[snb]

town name:

=IFERROR(MID(A1,4,LEN(A1)-10),"") 

postal code:

=IFERROR(MID(A1,4,LEN(A1)-10),"") 

In VBA:

Sub M_snb()
    [C1:D2000] = [if(A1:A2000="","",if(mod(column(C:D),2)=1,mid(A1:A2000,4,len(A1:A2000)-10),left(right(A1:A2000,6),5)))]
End Sub

[jolivanes]

Result in Columns B and C
Sub Maybe()

Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    c.Offset(, 1).Resize(, 2).Value = Array(Mid(c, 4, InStr(c, "(") - 5), Left(Mid(c, InStr(c, "(") + 1), Len(Mid(c, InStr(c, "(") + 1)) - 1))
Next c
End Sub

[jolivanes]

@snb, Post #3.
I guess the idea of code "In layman's terms" comes to mind

[snb]

@joli

Yes, but even if it's simple dutch, if you know nothing of dutch you won't understand anything.
There' no 'layman's' VBA.
Your solution is fortunately more VBA than #5.

[Sheepy1250]

I guess I might have oversold my VBA knowledge a little, guys - I understand the very basic functionality of some things so should be able to interpret the stuff you've very kindly offered!

Here goes nothing - thanks again everyone

[BIFanatic]

#10

Haha, correct, I thought he just wanted to have VBA enter the formula's for him

[Sheepy1250]

Or 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)
        
        'leading plus's
        Do While Left(s, 1) = "+"
            s = Right(s, Len(s) - 1)
        Loop
        
        'trailing )'s
        Do While Right(s, 1) = ")"
            s = Left(s, Len(s) - 1)
        Loop


        'start at 0,  i.e. v(0)
        v = Split(s, "(")
        
        '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

Hi Paul - thanks for this, it's almost perfect! Trying hard to locate where the final number of the string within the trailing brackets has managed to escape but my VBA knowledge isn't good enough to understand quite where yet - I'm going to hazard a guess that it may be somewhere within the final sub where the Trim command does its thing but not at all confident about that.

I am desperately trying to learn how to delve into the almost Cyrillic world of VB but am at the very start of my journey (if it's a journey then I'm just picking up the car keys in terms of where I've reached!!)

Thanks again though

Mark

[p45cal]

Another cat losing its pelt:

Sub blah()
With Range(Cells(1), Cells(1).End(xlDown)).Offset(, 1).Resize(, 2)
  .Columns(1).FormulaR1C1 = "=TRIM(MID(RC1,4,SEARCH(""("",RC1)-4))"
  .Columns(2).FormulaR1C1 = "=MID(RC1,SEARCH(""("",RC1)+1,5)"
  '.Value = .Value 'include this line to lose the formulae.
End With
End Sub

[jolivanes]

Re: Your solution is fortunately more VBA than #5
Thanks for the trust. Still learning from a.o. your contributions.
In a lot of cases I tend to go for "slower" solutions, looping where it can be done without as an example, because I think that they would be able to change the code easier if needed.
Many roads lead to Rome, or even Breda, "de parel van het zuiden."
For those that unfortunately don't speak Dutch, that means "the pearl of the south" (south as part of the country).

[Paul_Hossler]

Hi Paul - thanks for this, it's almost perfect! Trying hard to locate where the final number of the string within the trailing brackets has managed to escape but my VBA knowledge isn't good enough to understand quite where yet - I'm going to hazard a guess that it may be somewhere within the final sub where the Trim command does its thing but not at all confident about that.

I am desperately trying to learn how to delve into the almost Cyrillic world of VB but am at the very start of my journey (if it's a journey then I'm just picking up the car keys in terms of where I've reached!!)

Thanks again though

Mark

Didn't realize you wanted the Town number to go also. Look at the marked lines

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