Extract Data from a string

I am using Excel 2007 and I have a workbook with 12 different sheets. I have attached a sample of the field I need help with.

I need to extract the City/Town out of the branch field. The only pattern I can spot is that each branch begins with a number which is up to 4 numeric characters long, followed by a space and then the first part of a City/Town. This is followed by a space and either the second part of the town or another piece of information which I don't need.

I appreciate any help anyone can give me, either a formulae or VBA.

One way, in cell B2:

=IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2)+1,LEN(A2)),MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1))

This is basically looking for the two spaces and calculating and extracting the number of characters in between.

The first part, "IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1))" is checking to see if there is a *second* space; if not, you will get an error which is picked up by the ISERROR function.

If there is an error, the second part (the TRUE part of the IF statement), "MID(A2,FIND(" ",A2)+1,LEN(A2))", gives you the characters after the only space.

The third part (the FALSE part of the IF statement), "MID(A2,FIND(" ",A2)+1,LEN(A2)),MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)", gives you the characters between the two spaces.

Drag down to cell B103.


Regards

The only pattern I can spot is that each branch begins with a number which is up to 4 numeric characters long, followed by a space and then the first part of a City/Town. This is followed by a space and either the second part of the town or another piece of information which I don't need.

I thought it did what you described. I perhaps misinterpreted "the second part of the town". You may need to undertake a data cleansing exercise, at least for the "-" part of the problem. With regard to the name of a town, I'm not sure what you're going to do about that.

I suspect that you would have the same issue with a VBA solution.

Maybe someone else can help; I will be interested in the answer.

Regards

data is not consistent eg kings norton how would you know thats not just kings
you could compare each against a list of uk towns cities but even kings norton isnt in those lists as its an area of birmingham so youd need cities/towns/ district /borough lists
if you had the associated post codes it might be easier to extract from that but I'm not sure.
another example is glasgow hillington so why not london penge?

@ LL1980 all that does is remove the leading digits! and even then would fail if there were more than 4 or less than 3
the same thing you show can be accomplished with just
=MID(A2,FIND(" ",A2)+1,255)
it doesnt parse 144 Lytham St Annes-Graham to just Lytham St Annes

i think perhaps as a prelimary move
just find/replace *graham* with nothing first
then find replace (* with nothing
then find replace *p&h* with nothing
then have a look whats whats left

Hi dcaraher
maybe...

Function PatternExtractTwo(rngString As Range)
Dim oRgx As Object
Set oRgx = CreateObject("VBScript.RegExp")
With oRgx
    .IgnoreCase = True
    .Global = True
  .Pattern = "(-)"
       If .test(rngString) = True Then
      .Pattern = "([A-Za-z\s])+"
        PatternExtractTwo = .Execute(rngString)(0)
        Else
        .Pattern = "([A-Za-z]+)"
        PatternExtractTwo = .Execute(rngString)(0)
          End If
End With
End Function

you can probably do the same with out regular expresion

TMShucks
looks like the address names are like surnames with too many variations to able to filter all of them correctly

This UDF is a compromise but does thin the list fairly drastically.

Function RemoveMaxPossible(rngString As Range)
    Dim n As Integer
    Dim strData As String
    Dim arrCompanies() As Variant

    strData = Mid(rngString, InStr(1, rngString, " ") + 1)
    arrCompanies = Array("GRAHAM", "Grahams", "Graham", "Jewson", "NGB", "P&H", "P & H", "NJB", "Plumbing & Heatn", "Contrac")

    For n = 0 To UBound(arrCompanies)
        strData = WorksheetFunction.Substitute(strData, arrCompanies(n), "")
    Next

    If InStr(1, strData, "-") > 0 Then strData = Left(strData, InStr(1, strData, "-") - 1)

    If InStr(1, strData, "(") > 0 Then strData = Left(strData, InStr(1, strData, "(") - 1)

    If InStr(1, strData, Space(2)) > 0 Then strData = Left(strData, InStr(1, strData, Space(2)) - 1)

    RemoveMaxPossible = Trim(strData)

End Function

Add to the array of "company names" as examples are found.
And possibly add if statements to cover other delimiters as they reveal themselves.

The end result should allow you to choose whether the text is an address or a town name.

This is by no means ideal but might help some.
It might help with your geography if nothing else.