Can I use a cell to form part (only) of a regex?

[eteb3]

In a reply to another question @mikekaganski seemed to show a REGEX function that uses a cell for a condition(?)

My regex is for extracting a street address from a long string of many address items:
REGEX(W2,"([:digit:]+[:print:][:digit:]*) "&"[:print:]*"&"\b(ROAD|RD|STREET|ST|LANE|LN)\b")

I need a much longer list of possible road name parts than the six items (ROAD…LN) there.

I want to store these in a named cell elsewhere (concatenated with the pipes).

Can this be done? If so, what is the syntax, please?

Thanks for all help.

[fpy]

@eteb3

much longer

for extensive token list, also keep an eye on VLOOKUP

re+lookup.ods (9.6 KB)

[eteb3]

Thank you. You’ll have to excuse me, as a noob: what is a “token list”?

(I think, but am not sure, that your solution is working from the other end to my problem? I have an address string such as
GROUND FLOOR 123 ROSE HILL LANE CANNOCK CHASE BIRMINGHAM
and need to extract the street address “123 Rose Hill Lane”

[fpy]

@eteb3

token

sorry for the jargon (Lexical analysis - Wikipedia)

@eteb3

street address

indeed the list can be long :

• List of Streets in Birmingham, Borough of Birmingham,…*

• Non-metropolitan district - Wikipedia

[karolus]

Hallo
Assuming C11 has the text:
GROUND FLOOR 123 ROSE HILL LANE CANNOCK CHASE BIRMINGHAM

=REGEX(C11 ; "(?i)(\d+.*?(" & TEXTJOIN("|";1;$G$2:$G$10) & "))" )

returns: 123 ROSE HILL LANE

[fpy]

@karolus

returns

indeed works at ground floor,
but already fails at 1st floor

[karolus]

@fpy

but already fails at 1st floor

…but not on »second floor«

Of course! I have never claimed that a universal solution can be found in this way!

[eteb3]

but already fails at 1st floor

Yeah, I found similar.

I’m getting around that by separating all references to floors before I process it - which I need anyway

[fpy]

@eteb3

concatenated with

TEXTJOIN function

[eteb3]

Thank you - that’s a powerful function I didn’t know!

I still don’t know the syntax. I think I want =REGEX(A1,namedCell), but this doesn’t seem to work.

Using TEXTJOIN and “&”, namedCell returns
\b(road|rd|street|st|lane|ln)\b

but =REGEX(A1,namedCell) returns #N/A

EDIT: reason is that regex is case sensitive, and my data is in UPPER. Off I go to find how to do case-insensitive regex
EDIT2: This help page says in Options you can toggle Match case. I have nothing called Options on a Mac? But in Preferences > Calc > Calculate > General calcuations , the “Case sensitive” box is unchecked.
Any tips?

[fpy]

@eteb3

case-insensitive regex

Details_specific_to_REGEX_function
e.g. =REGEX("test","(?i)"& "TEST")

[eteb3]

@fpy

=REGEX("test","(?i)"& "TEST")

Thank you! What does & do, if you have time?

[fpy]

@eteb3

&

just normal concatenation.
same as =REGEX("test","(?i)TEST")

[karolus]

the REGEX-function works mostly independend of any setting in…

@eteb3

Preferences > Calc > Calculate