Splits octetten van IP-adres in Excel

Hebt u ooit geprobeerd IP-adressen in afzonderlijke kolommen te splitsen in een Excel-werkblad? De functie Tekst naar kolommen kan u weliswaar snel helpen bij deze taak, maar in dit artikel bespreken we enkele formules om dit in Excel voor elkaar te krijgen.

• Splits octetten van IP-adressen in afzonderlijke kolommen in Excel

Splits octetten van IP-adressen in afzonderlijke kolommen in Excel

Normaal gesproken helpen de functies LINKS, MIDDEL, LENGTE en ZOEKEN u elk octet van het IP-adres in afzonderlijke kolommen te extraheren. Volg hiervoor deze stappen:

Splits het eerste octet van de IP-adressen:

Om het eerste octet van het IP-adres te extraheren, past u de volgende formule toe in een lege cel:

Sleep vervolgens de vulgreep naar de cellen waarop u deze formule wilt toepassen. Alle eerste octetten van de IP-adressen zijn nu geëxtraheerd — zie screenshot:

Uitleg van de formule:

ZOEKEN(".",A2)-1: Deze ZOEKEN-functie geeft de positie van de eerste punt in cel A2 terug. Door 1 af te trekken, sluit u de punt zelf uit en krijgt u het resultaat 2.

LINKS(A2,ZOEKEN(".",A2)-1)=LINKS(A2, 2): De functie LINKS haalt de eerste 2 tekens uit de tekenreeks in cel A2.

Splits het tweede octet van de IP-adressen:

Voer de volgende formule in een lege cel in of kopieer deze om het tweede octet van de IP-adressen te verkrijgen:

Sleep vervolgens de vulgreep omlaag om deze formule in te vullen; u krijgt dan het gewenste resultaat, zie screenshot:

Uitleg van de formule:

1. ZOEKEN(".",A2)+1: Deze ZOEKEN-functie retourneert de positie van de eerste punt in cel A2. Door 1 op te tellen, begint de extractie bij het volgende teken — u krijgt dan het getal 4. Dit deel van de formule wordt herkend als het argument start_getal van de functie MIDDEL.

2. ZOEKEN(".",A2,ZOEKEN(".",A2)+1)-ZOEKEN(".",A2)-1: Dit deel wordt herkend als het argument aantal_tekens binnen de functie MIDDEL.

• ZOEKEN(".",A2,ZOEKEN(".",A2)+1)= FIND(".",A2,[[PH_26]]): Deze ZOEKEN-functie retourneert de positie van de tweede punt. Het resultaat is dan het getal 6.
• ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)-ZOEKEN(".",A2)-6-3=1: Trek de positie van de eerste punt af van de positie van de tweede punt, en trek vervolgens 1 af van het resultaat om de voorafgaande punt te verwijderen. Het resultaat is 2.

3. MIDDEL(A2,ZOEKEN(".",A2)+1,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)-ZOEKEN(".",A2)-1)=MIDDEL(A2, 4, 2): Ten slotte haalt deze MIDDEL-functie twee tekens op, te beginnen bij het vierde teken van cel A2.

Splits het derde octet van de IP-adressen:

Om het derde octet van de IP-adressen te extraheren, kunnen de functies MIDDEL en ZOEKEN u ook helpen. Voer de volgende formule in een lege cel in of kopieer deze:

Sleep vervolgens de vulgreep omlaag om de gewenste resultaten te verkrijgen; alleen het derde octet van de IP-adressen is nu gesplitst, zie screenshot:

Uitleg van de formule:

1. ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1: Deze geneste ZOEKEN-functie bepaalt de positie van de tweede punt in cel A2. Door er 1 bij op te tellen, begint de extractie bij het volgende teken — je krijgt dan het getal 7. Dit deel fungeert als het argument start_getal voor de functie MIDDEL.

2. ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)-(ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1): Dit deel van de formule wordt herkend als het argument aantal_tekens van de functie MIDDEL.

• ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)=FIND(".",A2, [[PH_51]]): Deze geneste ZOEKEN-functie bepaalt de positie van de derde punt in cel A2; het resultaat is het getal 8.
• ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)-(ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)=8-7: Trek de positie van de tweede punt af van die van de derde punt. Het resultaat is 1.

3. MIDDEL(A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1,ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)-(ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1))=MIDDEL(A2, 7, 1): Met deze MIDDEL-functie haalt u één teken op dat begint bij het zevende teken van cel A2.

Splits het vierde octet van de IP-adressen:

De onderstaande formule helpt u het laatste octet van het IP-adres te extraheren. Kopieer of voer de formule in een lege cel in:

Sleep vervolgens de vulgreep om deze formule in te vullen in de andere gewenste cellen; u krijgt dan het resultaat zoals hieronder in de screenshot wordt weergegeven:

Uitleg van de formule:

1. ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)+1: Deze geneste ZOEKEN-functie bepaalt de positie van de derde punt in cel A2. Door 1 op te tellen, begint de extractie bij het volgende teken — u krijgt dan het getal 9. Dit deel fungeert als het argument start_getal voor de functie MIDDEL.

2. LENGTE(A2)-ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1):

• LENGTE(A2): De functie LENGTE geeft het totale aantal tekens in cel A2 terug – en dat is 11.
• LENGTE(A2)-ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)=11-8: De totale lengte van cel A2 minus de positie van het derde punt geeft het aantal resterende tekens. Zo verkrijgt u het getal 3.

3. MIDDEL(A2,ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1)+1,LENGTE(A2)-ZOEKEN(".",A2,ZOEKEN(".",A2,ZOEKEN(".",A2)+1)+1))=MIDDEL(A2, 9, 3): Tot slot haalt deze MIDDEL-functie drie tekens op, te beginnen bij het negende teken van cel A2.

Gebruikte relatieve functies:

Meer artikelen: