I have a spreadsheet which uses concatenated data as the basis for sumif
calculations looking at another spreadsheet. In separate columns however, I
want to be able to split out the concatenated data.

Column AM has my concatenated data as follows:

Glasgow Property Freehold
South Yorkshire Property Shorthold Lease
Edinburgh Other Telephone System

I want to be able to search in column AM, find the Region (Glasgow,
Edinburgh....) and return that region into column B of the same row. I have
approx 20 regions that I am looking for which are listed in a range called
"Opcos"

I think it's vbTextCompare I need to use but don't know where to start with
writing the code to do this. Could someone please help me with the code

Try this


Sub ExtractLocation()


LastrowF = Cells(Rows.Count, "F").End(xlUp).Row
Set Opcos = Range(Cells(1, "F"), Cells(LastrowF, "F"))

LastrowAM = Cells(Rows.Count, "AM").End(xlUp).Row
Set AMRange = Range(Cells(1, "AM"), Cells(LastrowAM, "AM"))


For Each cell In Opcos

Set c = AMRange.Find(cell, LookIn:=xlValues)
If Not c Is Nothing Then
Cells(cell.Row, "B") = cell

End If

Next cell

End Sub

"Karen McKenzie" wrote:

I have a spreadsheet which uses concatenated data as the basis for sumif
calculations looking at another spreadsheet. In separate columns however, I
want to be able to split out the concatenated data.

Column AM has my concatenated data as follows:

Glasgow Property Freehold
South Yorkshire Property Shorthold Lease
Edinburgh Other Telephone System

I want to be able to search in column AM, find the Region (Glasgow,
Edinburgh....) and return that region into column B of the same row. I have
approx 20 regions that I am looking for which are listed in a range called
"Opcos"

I think it's vbTextCompare I need to use but don't know where to start with
writing the code to do this. Could someone please help me with the code

KAren,

Sub FindOpCos()

Dim myAM As Range
Dim myCell As Range

For Each myAM In Range("AM1", Range("AM65536").End(xlUp))
For Each myCell In Range("Opcos")
If InStr(1, myAM.Value, myCell.Value) 0 Then
Cells(myAM.Row, 2).Value = myCell.Value
GoTo Found:
End If
Next myCell
Found:
Next myAM

End Sub

HTH,
Bernie
MS Excel MVP


"Karen McKenzie" wrote in message
...
I have a spreadsheet which uses concatenated data as the basis for sumif
calculations looking at another spreadsheet. In separate columns however, I
want to be able to split out the concatenated data.

Column AM has my concatenated data as follows:

Glasgow Property Freehold
South Yorkshire Property Shorthold Lease
Edinburgh Other Telephone System

I want to be able to search in column AM, find the Region (Glasgow,
Edinburgh....) and return that region into column B of the same row. I have
approx 20 regions that I am looking for which are listed in a range called
"Opcos"

I think it's vbTextCompare I need to use but don't know where to start with
writing the code to do this. Could someone please help me with the code

It runs but doesn't do what I want it to. Should I be replacing the "f" with
something?

"Karen McKenzie" wrote:

I have a spreadsheet which uses concatenated data as the basis for sumif
calculations looking at another spreadsheet. In separate columns however, I
want to be able to split out the concatenated data.

Column AM has my concatenated data as follows:

Glasgow Property Freehold
South Yorkshire Property Shorthold Lease
Edinburgh Other Telephone System

I want to be able to search in column AM, find the Region (Glasgow,
Edinburgh....) and return that region into column B of the same row. I have
approx 20 regions that I am looking for which are listed in a range called
"Opcos"

I think it's vbTextCompare I need to use but don't know where to start with
writing the code to do this. Could someone please help me with the code

ry changing this line

from:
Cells(cell.Row, "B") = cell
to:
Cells(c.Row, "B") = cell

"Karen McKenzie" wrote:

It runs but doesn't do what I want it to. Should I be replacing the "f" with
something?

"Karen McKenzie" wrote:

I have a spreadsheet which uses concatenated data as the basis for sumif
calculations looking at another spreadsheet. In separate columns however, I
want to be able to split out the concatenated data.

Column AM has my concatenated data as follows:

Glasgow Property Freehold
South Yorkshire Property Shorthold Lease
Edinburgh Other Telephone System

I want to be able to search in column AM, find the Region (Glasgow,
Edinburgh....) and return that region into column B of the same row. I have
approx 20 regions that I am looking for which are listed in a range called
"Opcos"

I think it's vbTextCompare I need to use but don't know where to start with
writing the code to do this. Could someone please help me with the code

Karen,

Joel's method is also written to only find each Opcos value once.... I think mine will do what you
want.

HTH,
Bernie
MS Excel MVP


"Karen McKenzie" wrote in message
...
It runs but doesn't do what I want it to. Should I be replacing the "f" with
something?

"Karen McKenzie" wrote:

I have a spreadsheet which uses concatenated data as the basis for sumif
calculations looking at another spreadsheet. In separate columns however, I
want to be able to split out the concatenated data.

Column AM has my concatenated data as follows:

Glasgow Property Freehold
South Yorkshire Property Shorthold Lease
Edinburgh Other Telephone System

I want to be able to search in column AM, find the Region (Glasgow,
Edinburgh....) and return that region into column B of the same row. I have
approx 20 regions that I am looking for which are listed in a range called
"Opcos"

I think it's vbTextCompare I need to use but don't know where to start with
writing the code to do this. Could someone please help me with the code