CODEWITHSUNDEEP
excelvbastring
Emmanuelle Crayon
Emmanuelle Crayon

Reputation: 63

Extract string by position

I have a row with countries separated by ;. This is an example located in a cell: RO;BE;AU;CN;DE;UK;ES;HU;NL;SE;AT;EL;CZ.

On the other hand, I have another cell with organizations, also separated with ;: e.g Org1; Org2; Org3... and it has the same position as the countries.

Now, I have to find the position of a country (e.g. DE) and extract the organization in the same position in the other cell.

What I have identified is the position by using FIND formula. Now I need to extract the organization separated by commas but I do not know how to do it. I've tried LEFT, RIGHT function... Also INDIRECT.

Any comments would be appreciated.

Upvotes: 0

Views: 460

Answers (5)

Eddy
Eddy

Reputation: 5370

Just for the fun of it.

It can be done with the basic excel formula's as long as your really never want to look at the formula again and don't care about the performance.

How it looks in Excel

And here are all the used formula's

=FIND(";" & B4 &";";";" & B2 & ";")
=LEFT(B2;C6)
=LEN(C7)-LEN(SUBSTITUTE(C7;";";""))
=SUBSTITUTE(B3;";";"|";C8)
=FIND("|";C9)
=FIND(";";B3&";";C10+1)
=MID(C9;C10+1;C11-C10-1)

=MID(B3;FIND("|";SUBSTITUTE(B3;";";"|";LEN(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";")))-LEN(SUBSTITUTE(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";"));";";""))))+1;FIND(";";B3&";";FIND("|";SUBSTITUTE(B3;";";"|";LEN(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";")))-LEN(SUBSTITUTE(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";"));";";""))))+1)-FIND("|";SUBSTITUTE(B3;";";"|";LEN(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";")))-LEN(SUBSTITUTE(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";"));";";""))))-1)

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Because you've tagged the question with VBA, you can use an UDF to get the value.

This UDF got three arguments:

  1. Cell of countries
  2. Cell of orgs

  3. Country you are looking for

    Function GET_ORG(ByVal vCountries As Range, ByVal vOrgs As Range, ByVal vThisCountry As String) As String
Dim Countries As Variant
Countries = Split(vCountries.Value, ";")

Dim Orgs As Variant
Orgs = Split(vOrgs.Value, ";")

Dim i As Byte

For i = LBound(Countries) To UBound(Countries) Step 1
    If Countries(i) = vThisCountry Then Exit For
Next i

GET_ORG = Orgs(i)

Erase Countries
Erase Orgs

End Function

The test I made:

enter image description here

In cell D2 my formula is =GET_ORG(A1;A2;D1)

Upvotes: 1

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

If you have Excel 2013 or higher then you can use FILTERXML approach as shown by @JvdV. For all versions you can use:

=LOOKUP(2,1/(TRIM(MID(SUBSTITUTE(";"&B2,";",REPT(" ",99)),ROW($A$1:$A$10)*99,99))=B4),TRIM(MID(SUBSTITUTE(";"&B3,";",REPT(" ",99)),ROW($A$1:$A$10)*99,99)))

Where

Cell B2 holds RO;BE;AU;CN;DE;UK;ES;HU;NL;SE;AT;EL;CZ Cell B3 holds Org1; Org2; Org3.. Cell B4 holds the key you are looking for e.g. RO.

It is assumed that all entries in the source list are unique and there are 10 entries to be split. If you have more then you need to change ROW($A$1:$A$10) to maximum entries to suit your case like ROW($A$1:$A$99) etc.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

It's simple with macro written in VBA:

Option Explicit
Sub ExtractOrganization()
    Dim i As Long, countries As Variant, organizations As Variant, country As String
    ' take lists from cells and split them by ;
    countries = Split(Cells(1, 1), ";")
    organizations = Split(Cells(2, 1), ";")
    country = "BE"
    For i = LBound(countries) To UBound(countries)
        If countries(i) = country Then Exit For
    Next

    MsgBox organizations(i)
End Sub

And here's my sheet setup:

enter image description here

Upvotes: 1

RameshN
RameshN

Reputation: 530

Using SPLIT, MATCH AND INDEX excel formulas you can get it

FORMULA

=INDEX(SPLIT(B2, ";"), MATCH(B3, SPLIT(B1, ";")))

EXAMPLE:

Apply above formula at B4 cell

    A               B

1   COUNTRIES       A;B;C;D;E
2   ORG             Org1;Org2;Org3;Org4;Org5
3   Search CODE     B
4   Result          Org2 (=INDEX(SPLIT(B2, ";"), MATCH(B3, SPLIT(B1, ";"))))

Upvotes: -2

Related Questions