Find last IP address in cell text

[JCTalk]

Hiya,

I have a column in my workbook that has IP addresses separated by ", " (comma space). I'm trying to pick out the last IP address (from the right of the cell) that starts with either one or more criteria.

I've managed to locate

 =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)))

to return the last IP address, but I'm having trouble finding one to bring me back the last IP that begins with "10." or "11."

I'd like to be able to use multiple beginning 1st octet(?). Presume this may require an array formula.

Many thanks all in advance for any help you can offer.

 

[bosco_yip]

Extract last IP that begins with "10." or "11."

Try this,

=IF(OR(LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))),3)={"10.","11."}),TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))),"")

or, this shorter,

=TRIM(MID(A1,MIN(FIND({" 10."," 11."},A1&" 10."&" 11.")),99))

Regards
Bosco

 

[Faseeh]

Can you post some sample data?

 

[JCTalk]

Can you post some sample data?

Hi Faseeh,

I've attached an example of what I'm looking to achieve. I'm trying to bring back the first IP address from the right that matches the starting criteria.

So in this case, if the criteria were "10." it should bring back as shown in the worksheet. If the criteria was "10." and "11.", it should still bring back the 10. address shown as it is the IP address furthest to the right.

Many thanks

 

[JCTalk]

Try this,

=IF(OR(LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))),3)={"10.","11."}),TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))),"")

or, this shorter,

=TRIM(MID(A1,MIN(FIND({" 10."," 11."},A1&" 10."&" 11.")),99))

Hi Bosco_yip,

Unfortunately neither of these worked for me. The first returned a blank cell (when done as an array or normal), and the second one returned all "10." addresses, I'm looking to bring back the first IP address that begins with one of the criteria's from the right of cell (new data is appended to the end of the cell you see, so that is my most recent IP).

Many thanks

 

[bosco_yip]

Hi Bosco_yip,

Unfortunately neither of these worked for me. The first returned a blank cell (when done as an array or normal), and the second one returned all "10." addresses, I'm looking to bring back the first IP address that begins with one of the criteria's from the right of cell (new data is appended to the end of the cell you see, so that is my most recent IP).

Many thanks

Extract the last IP that begin with "10." or "11."

In B2, formula copy down :

=IFERROR(LEFT(MID(A2,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A2," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A2))))),LEN(A2)),FIND(",",A2)-1),"")

Regards
Bosco

 

[JCTalk]

View attachment 42510=IFERROR(LEFT(MID(A2,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A2," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A2))))),LEN(A2)),FIND(",",A2)-1),"")

Hi Bosco,

Thank you that works great for a large majority.

I have noticed that there are a few situations where I end up with either too little characters, or too many (get a comma at the end).

I've reattached your sheet with two examples. Any idea why this would be happening?

 

[Chihiro]

I have noticed that there are a few situations where I end up with either too little characters, or too many (get a comma at the end)

In your original sample, 1st IP address was either same length or longer than IP of interest. Hence, why Bosco's formula used LEFT(Formula,FIND(",",A3)-1) and worked.

You can replace A3 in FIND portion with MID() portion of Bosco's formula, concatenated with ",".

=IFERROR(LEFT(MID(A8,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A8," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A8))))),LEN(A8)),FIND(",",MID(A8,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A8," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A8))))),LEN(A8))&",")-1),"")

 

[bosco_yip]

Hi Bosco,

Thank you that works great for a large majority.

I have noticed that there are a few situations where I end up with either too little characters, or too many (get a comma at the end).

I've reattached your sheet with two examples. Any idea why this would be happening?

Or this shorter :

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A2," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A2))))),LEN(A2)),",",REPT(" ",50)),50)),"")

Regards
Bosco

 

[Chihiro]

Ah, forgot about REPT(" ",). So many things to remember

 

[JCTalk]

Or this shorter :

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A2," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A2))))),LEN(A2)),",",REPT(" ",50)),50)),"")

Regards
Bosco

Many thanks Bosco, that's fantastic. Works perfectly! Thats saved me a lot time and headache lol. Its really fast as well. Brilliant work thanks again.

In your original sample, 1st IP address was either same length or longer than IP of interest. Hence, why Bosco's formula used LEFT(Formula,FIND(",",A3)-1) and worked.

You can replace A3 in FIND portion with MID() portion of Bosco's formula, concatenated with ",".

=IFERROR(LEFT(MID(A8,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A8," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A8))))),LEN(A8)),FIND(",",MID(A8,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A8," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A8))))),LEN(A8))&",")-1),"")

Thank you for your reply Chihiro. I went with Bosco's shorter version in the end, but I sincerely appreciate your help. Thank you.

 

[bosco_yip]

Or this shorter :

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A2," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A2))))),LEN(A2)),",",REPT(" ",50)),50)),"")

Regards
Bosco

1 Substitute function can be removed, the formula further reduced to :

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,LOOKUP(9^9,FIND(" 11.",SUBSTITUTE(" "&A2," 10."," 11."),ROW($1:$99))),99),",",REPT(" ",99)),99)),"")

Regards
Bosco