CODEWITHSUNDEEP
stringexcel
Tactically Superior Avocado
Tactically Superior Avocado

Reputation: 45

Is it possible to check a generic part of a string in Excel?

Given an IP in string form (XX.XXX.YYY.XX) is it possible to check the values of YYY and copy them into an adjacent cell?

The value will either be 2 or 3 characters long and will always come after the 2nd period.

Answered below by Jerry. Thank you again Jerry!

Upvotes: 2

Views: 118

Answers (1)

Jerry
Jerry

Reputation: 71578

You can extract YYY into a cell with the formula:

=MID(A1,FIND("@",SUBSTITUTE(A1,".","@",2))+1,FIND(".",A1,FIND("@",SUBSTITUTE(A1,".","@",2))+1)-FIND("@",SUBSTITUTE(A1,".","@",2))-1)

Assuming the IP is in A1.

It works for whatever length of IP you have.

EDIT: Some details:

There's some bits of recycled formulas here. SUBSTITUTE(A1,".","@",2) returns [email protected] (substitute the 2nd occurrence of the dot in A1 by @) We'll use this in the big formula and let's call it R for the time being.

This turns the formula into:

=MID(A1,FIND("@",R)+1,FIND(".",A1,FIND("@",R)+1)-FIND("@",R)-1)
        ^-----------^ ^--------------------------------------^
            Start     |           1            | |     2     |
                                      Length

Much better!

The starting position part:

FIND("@",R)+1 returns the position of the character just after @, so that MID starts with the first Y. Here, the position becomes 7.

The length position part:

  1. FIND(".",A1,FIND("@",R)+1) There's a formula we already used here, FIND("@",R)+1 is 7 so that we have: FIND(".",A1,7). This finds the position of the dot in A1 which is after or at the 7th character. This one gets the value 10.

  2. This one should be familiar and gets the position of @ in R, which is 6.

10-6 gives 4, which is one character longer than what we're looking for. (because we're dealing with ranked positions; e.g. Length of the string between 1st and 3nd character of a string is 1 but 3-1 gives 2)

Hence why there's the final -1 part.

Upvotes: 1

Related Questions