Which wildcard characters should I use?

Question

Please help

"string_mod_4"
John Morgen A1
Ali Zayas
Jennifer Benz A2
Desmond Michael Hall A3
Erik Remar
...

At the end of the text, I cannot remove 'A1', 'A2', etc.

they do not work

Replace([string_mod_4];"*A[123456789]";"";1)
Replace([string_mod_4];"*A?";"";1)
Replace([string_mod_4];"*A#";"";1)

Replace does not know wildcards, use something like Access vba Replace/Regex — Luuk
– Luuk, Commented Nov 24, 2024 at 10:45

June7 · Accepted Answer · 2024-11-25 03:55:46Z

1

Consistency of structure is critical in string manipulation.

Assuming there is an A preceding a single digit at end of string (when present at all), consider:

Trim(IIf(x LIKE "*A#", Replace(x, Right(x, 2), ""), x))

Any variation from that assumption will complicate.

If the field is Null, expression will error. In a query, one way to handle that possibility:
IIf(Not x Is Null, Trim(IIf(x LIKE "*A#", Replace(x, Right(x, 2), ""), x)))

RegEx could be more flexible and compact but has a fairly steep learning curve. Also, AFAIK cannot be called directly in query. Would have to build a custom function to perform manipulation and return value. Review Microsoft office Access `LIKE` VS `RegEx`

edited Nov 25, 2024 at 3:55

answered Nov 25, 2024 at 0:08

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Which wildcard characters should I use?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related