Extract text strings from cell ( regex available ) - mac

[alex0182828]

I would like to be able to pull text strings matching this regex from a cell that would contain them ( cell also contains other words and may have the value as part of an url ^0x[a-fA-F0-9]{40}$ 0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96 http://www.blahblah.com/0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96/wafflewaffle.cake​ However im on a mac and it seems the VBA addon to do this doesnt exisit. Can anyone help me ?

 

[JvdV]

Unfortunately the regex library is not available (AFAIK). Since you are using Excel 2012 for mac, maybe try the following CSE-entered formula:

=MID(A1,MIN(IF(FREQUENCY(IF(ISNUMBER(SEARCH(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1),"abcdef0123456789")),ROW(A1:INDEX(A:A,LEN(A1)))),IF(ISNUMBER(SEARCH(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1),"abcdef0123456789")),0,ROW(A1:INDEX(A:A,LEN(A1)))))=40,ROW(A1:INDEX(A:A,LEN(A1))),LEN(A1)+1))-42,42)

This does **not** check the leading two characters are '0x', but if need be, this can be included in the above which would make it at least twice as long.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do you always want to return the text before the last /

 

[alex0182828]

thanks all i have updated my profile now thanks im on Office 365 Mac v16.69

The string does need to start with 0x and it should pull it no matter what the surrounding text or lack of so i have given 5 examples of cell context it could appear

0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96

http://www.blahblah.com/0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96/wafflewaffle.cake

http://www.blahblah.com/0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96

this is a great one 0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96

check this out 0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96 looks good

 

[JvdV]

Well, that would make life a lot easier indeed. Not sure if all functions would be available (yet), but try:

Book1
	A	B
1	0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96	0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96
2	http://www.blahblah.com/0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96/wafflewaffle.cake	0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96
3	http://www.blahblah.com/0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96	0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96
4	this is a great one 0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96	0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96
5	check this out 0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96 looks good	0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96
6	0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b97 or 0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b96	0xc8e78ad2573f5e16a286443aea2a6f1ba0c06b97
Sheet5

Cell Formulas
Range	Formula
B1:B6	B1	=MAP(TOCOL(A:A,3),LAMBDA(r,LET(x,SEQUENCE(LEN(r)+1),y,MID(r&"|",x,1),z,ISNUMBER(SEARCH(y,"abcdef0123456789")),v,MID(r,FILTER(x,DROP(FREQUENCY(IF(z,x),IF(z,0,x))=40,-1))-42,42),@FILTER(v,EXACT(LEFT(v,2),"0x")))))
Dynamic array formulas.

The idea here is that `MAP()` will iterate over each value in column A and will return the 1st occurence of a valid pattern, hence the implicit intersection on the last `FILTER()`.

 

[alex0182828]

amazing it works perfectly your a

 

[JvdV]

I'm glad it worked. For good measure, you can replace the '=40' part into '>39' since the patterns could then also be found if they are overlapping eachother.

 

[Georgiboy]

If it always starts with '0x' and the string is always the same length and you have the textafter function then the below is a shorter formulae:

=LEFT("0x" & TEXTAFTER(A1,"0x"),42)

If the string varies in length then it will not be an option

 

[alex0182828]

Cool @Georgiboy thats superuseful aswell as maybe can use it in other forumlas without my head exploding