Remove everything left of the dash (1 Viewer)

alcifer_c

Registered User.
Local time
Today, 20:45
Joined
Jun 3, 2002
Messages
42
Hello,

I have data that looks like this:

blahblah-need
blah-aneed
blahblahblah-aaneed

I would like to have everything right of the dash to get:

need
aneed
aaneed

I have used mid and instr to get the left hand stuff. I've tried to modify to get the data to the right of the dash and I am lost.

Your help appreciated.
 

KenHigg

Registered User
Local time
Today, 16:45
Joined
Jun 9, 2004
Messages
13,327
right(MyText,instr(MyText,"-")+1)

???
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:45
Joined
Feb 19, 2002
Messages
42,976
InStr() is used to find the first "-" and then you use that position +1 as the starting place for the Right() function.

Right(YourField, InStr(1,YourField, "-") + 1)
 

supercharge

Registered User.
Local time
Today, 13:45
Joined
Jun 10, 2005
Messages
215
Edited:

Here are the steps: Let's use record "blahblah-need" as an example
-Use Len to find the number characters, return 13
-Use for loop to find where the "-" is,
-Set Index = counter
-Use Mid to return the data needed
FinalData = Mid(String, Index+1)
-Store FinalData

Ya beat me all, Urrrr...
 
Last edited:

alcifer_c

Registered User.
Local time
Today, 20:45
Joined
Jun 3, 2002
Messages
42
There are several dashes

Sorry, I should have mentioned that there can be several dashes

so the data looks like:

blah-blah-need
blah-needa
blah-blah-blah-aneed

need to look like:

need
needa
aneed

I had tried the expression you suggested already and thought that I was using incorrectly. I guess the data needs to be read right to left to look for the "-".

Other suggestions?
 

supercharge

Registered User.
Local time
Today, 13:45
Joined
Jun 10, 2005
Messages
215
alcifer_c said:
I guess the data needs to be read right to left to look for the "-".

My For-loop will do the trick.
Set an Index variable = counter when it finds a dash.
Put the Mid statement outside of the loop because once the loop finishes, the Index will be the last dash's index.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:45
Joined
Feb 19, 2002
Messages
42,976
If you are using a newer version of Access, it will contain the "InStrRev()" function which starts the search from the right instead of from the left. So the expression would be:
Right(YourField, InStrRev(YourField, "-") + 1)

supercharge,
I know you're trying and I don't want to discourage that, but why would you want to write a code loop when a simple expression is all that is necessary?
 

alcifer_c

Registered User.
Local time
Today, 20:45
Joined
Jun 3, 2002
Messages
42
Thank you all

Right([YourField],Len([yourfield])-InStrRev([YourField],"-"))

does exactly what I was looking for.

my .02 ... whatever works, works BUT there is an eloquence to simplicity.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:45
Joined
Feb 19, 2002
Messages
42,976
Right(YourField, InStrRev(YourField, "-") + 1)
sorry I was thinking Mid() and so this should be:

Mid(YourField, InStrRev(YourField, "-") + 1)

But the Len() version works also.
 

Charles2019

New member
Local time
Today, 15:45
Joined
Mar 24, 2020
Messages
22
I have data in the following format
CN=AAD-VPNMFA-ALL,OU=Security Groups,DC=myhouse,DC=corp,DC=com
and want to remove everything to the right of the first comma. In Excel, I can do the following =LEFT(B2,FIND(",",B2)-1) and everything to the right of the first comma is removed. This works but there are 74 columns and I need a way to clear this with an access query so I can repeat this process . I tried this statement =Right([OU1],Len([OU1])-InStrRev([OU1],",")) but get a data mismatch error. Also tried using LEFT with similar results.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:45
Joined
Jan 20, 2009
Messages
12,849
Can be cases with no dash? If so then concatenate one before or after (depending on the strategy used) to avoid errors.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:45
Joined
Feb 19, 2013
Messages
16,553
better not to hijack a 15 year old thread and start your own - but try

left(OU1, instr(OU1,",")-1)

instr is the vba equivalent of excel's find function

don't understand your comment

but there are 74 columns and I need a way to clear this with an access query so I can repeat this process
 

Charles2019

New member
Local time
Today, 15:45
Joined
Mar 24, 2020
Messages
22
better not to hijack a 15 year old thread and start your own - but try

left(OU1, instr(OU1,",")-1)

instr is the vba equivalent of excel's find function

don't understand your comment
CJ,
My apologies for grabbing an old post, it came up during my search. The comment, I have this in Excel and there are 74 columns that represents the 74 OU's. with the various security groups.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:45
Joined
Feb 19, 2013
Messages
16,553
if you are trying to apply excel methods to access tables you will struggle. Excel typically stores data horizontally, databases store data vertically i.e. you would have 74 rows
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:45
Joined
Jan 20, 2009
Messages
12,849
CJ,
My apologies for grabbing an old post, it came up during my search. The comment, I have this in Excel and there are 74 columns that represents the 74 OU's. with the various security groups.
Are you recording this manually?
Did you know that Active Directory can be queried directly?

 

Users who are viewing this thread

Top Bottom