Remove Characters After WildCard (1 Viewer)

Charles2019

New member
Local time
Today, 13:16
Joined
Mar 24, 2020
Messages
22
I've got a data string that has an * in it followed by a comma with data with characters after it. I need to remove the * and all of the characters to the right after it. The range of characters vary in length. Data Sample;
CSQ_ACNY_Team1*, CSQ_ACNY_AllOther
CSQ_ACNY_Team1*
I've tried using this statement Replace([CSQ Names],Chr(42),"")
 

Micron

AWF VIP
Local time
Today, 14:16
Joined
Oct 20, 2018
Messages
3,476
Because a limit isn't imposed by that, Mid will return everything right of the find, yes? We want Left, no?
 

plog

Banishment Pending
Local time
Today, 13:16
Joined
May 11, 2011
Messages
11,611
Yes, I messed up my initial post, but according to Charles' second post I screwed up and got it right. Here's the right code depending on what you actually want:

First post:

I need to remove the * and all of the characters to the right after it.

NewField: Mid([YourField], 1, InStr([YourField], "*")-1)



Second post:

Yes, I want everything to the right returned

NewField: Mid([YourField], InStr([YourField], "*"))
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:16
Joined
Feb 28, 2001
Messages
26,999
There is also a RIGHT(string,n) function that you could use to recover the rightmost N characters.


 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:16
Joined
Jan 20, 2009
Messages
12,849
BTW Another variation is using InStrRev() to find the last instance of a character. Useful if the character occurs more than once.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:16
Joined
May 7, 2009
Messages
19,169
use Update Query:

Update yourtable Set [CSQ Names] = Left([CSQ Names], Instr([CSQ Names], "*")-1) Where Instr([CSQ Names], "*") > 0;
 

Charles2019

New member
Local time
Today, 13:16
Joined
Mar 24, 2020
Messages
22
There is also a RIGHT(string,n) function that you could use to recover the rightmost N characters.



Running the RIGHT(string,n) function removes the characters, just the varying length that it doesn't cover.
 

Charles2019

New member
Local time
Today, 13:16
Joined
Mar 24, 2020
Messages
22
Yes, I messed up my initial post, but according to Charles' second post I screwed up and got it right. Here's the right code depending on what you actually want:

First post:



NewField: Mid([YourField], 1, InStr([YourField], "*")-1)



Second post:



NewField: Mid([YourField], InStr([YourField], "*"))
Plog, Thanks for your help. This works with the exception of the string that do not have the * in the string.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:16
Joined
May 7, 2009
Messages
19,169
if this is an Expression in a Query:

ColumnName: IIF(Instr([CSQ Names], "*") > 0, Left([CSQ Names], Instr([CSQ Names], "*")-1), [CSQ Names])
 

Users who are viewing this thread

Top Bottom