• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pulling part of the cell out of the cell

L

lwilt

Member
I'm trying to find a function in excel or even access that could do the following:

ZAB103-3012
ZAF405-HD-0001

Turn those cells into:

AB103
AF405-HD

Pulling everything to the right of the Z in the last step is the easy part but I can't figure out what function would be able to find the last "-" in the cell and pull everything to the left of it. If this can be done in Access then that would be even better because I'm going to have over 500,000 records with multiple attributes if I take it in excel.

thanks
-Logan
 
Try something like in Access

PHP: 
SELECT MID(MyField,IIF(StrConv(LEFT(MyField,1),1)="Z",2,1),InStrRev([MyField],"-")-IIF(StrConv(LEFT(MyField,1),1)="Z",2,1)) AS MyNewField
FROM MyTable
 
Hi Logan ,

I am not sure about the complexity of your data , but the data you have posted does not seem so :

1. If every item of data is going to start with a "Z" , then eliminating it is quite easy.

2. If every item of data is going to have 4 characters after the last hyphen , again things are easy.

Is this so , or is your data more complex ? If so , can you post as many of these items as possible to know the extent of data variability ?

Narayan
 
Looking at the two examples you gave, it seems that you just remove the first and the last 5 characters. If that is the case, then for Excel you can do:
Code: 
=MID(A2,2,LEN(A2)-6)
Where A2 is the target cell
 
OK.. Here you go.. Thanks to the previous posts on the web with slightly modified to your requirements.

=MID(A1,SEARCH("Z",A1,1)+1,SEARCH("^^",SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-SEARCH("Z",A1,1)-1)


Just make sure that you do not have "^^" character repeated anywhere on the original list of data you have.
 
that worked thanks

I also found another way messing with it if I just use InStrRev() to find "-" and then using midpoint and using -2 on what the InStrRev() string returns
 
Try something like in Access

PHP:
SELECT MID(MyField,IIF(StrConv(LEFT(MyField,1),1)="Z",2,1),InStrRev([MyField],"-")-IIF(StrConv(LEFT(MyField,1),1)="Z",2,1)) AS MyNewField
FROM MyTable​
Kris
Click to expand...
Hi, Krishnakumar!
Nice solution.
Regards!
 
You must log in or register to reply here.
Back
Top