I’m a beginner in access but when it comes to excel and excel formulas I could come up with some amazing formulas. I'm working on a access database and what I’m trying to accomplish is as follows
I have a table that’s attached to a txt file that is constantly updating once a week the important thing is that the description for the items in the txt file come pretty misstep for example
MOD ASSORTMENT TRAY
KSK MEAL BAR CHO P/B29112
KSK MEAL BAR CHO CHP34389
PB OTML/CH COOKIE 55293
H JACK APL CINN P/CK02543
MCOR CELERY FLAKES 00706
KEL FS DSNY PH&FRB 55664
KLEENEX 100CT FCL 25849
FLOR MAXI THN W/WING00204
PAR WHT BATH TISSUE 55514
GLAD O/S TLKTC DRSTR78365
SYL 60WT DAYLITE 11463
XTRA 175OZ TRPCL PSN41742
GAIN PWD WBLCH 53LD 81469
GAIN PWD SF 53LD 81470
SUN PWD CLN & FRESH 70880
SUN PWD CLN W/ FEB
What I'm trying to do is take out the string of number that is at the end of every description if it has a string of digits, but if it has the string of digits at the end its always 5 digits no more or less. I came up with a formula for excel but it doesn't work in access
this is the formula that im using in excel (i also attached a snapshot of the formula)
=IF((IFERROR(((RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ",""))))))-(RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ","")))))))=0,0))=TRUE,(TRIM(LEFT(D10,(LEN(D10)-5)))),(IF(LEN(RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ",""))))))<5,D10,(IF(LEN(RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ",""))))))>5,(LEFT(D10,(LEN(D10)-5))))))))
more simplified
=IF((IFERROR((K10-K10)=0,0))=TRUE,(TRIM(LEFT(D10,(LEN(D10)-5)))),(IF(LEN(K10)<5,D10,(IF(LEN(K10)>5,(LEFT(D10,(LEN(D10)-5))))))))
K10=RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ","")))))
what is does basically it gives me everything after the last space in the string or char for example if I have
SUN PWD CLN & FRESH 70880
k10 will equal to 70880
or if I have
GLAD O/S TLKTC DRSTR78365
k10 will equal to DRSTR78365
or if I have
SUN PWD CLN W/ FEB
k10 will equal FEB
here is where the rest of the formula will come to effect if k10-k10=0 in the fisrt example the whole formula will equal SUN PWD CLN & FRESH, if k10-k10 is an error in the second and third examples then the formula will evaluate is the length of k10 grader then 5 return the whol string of char minus the last five char, but if its less return the whole string of char.
Can anyone help me please come up with the same formula or a different solution for my problem.
Thank you in advance...
I have a table that’s attached to a txt file that is constantly updating once a week the important thing is that the description for the items in the txt file come pretty misstep for example
MOD ASSORTMENT TRAY
KSK MEAL BAR CHO P/B29112
KSK MEAL BAR CHO CHP34389
PB OTML/CH COOKIE 55293
H JACK APL CINN P/CK02543
MCOR CELERY FLAKES 00706
KEL FS DSNY PH&FRB 55664
KLEENEX 100CT FCL 25849
FLOR MAXI THN W/WING00204
PAR WHT BATH TISSUE 55514
GLAD O/S TLKTC DRSTR78365
SYL 60WT DAYLITE 11463
XTRA 175OZ TRPCL PSN41742
GAIN PWD WBLCH 53LD 81469
GAIN PWD SF 53LD 81470
SUN PWD CLN & FRESH 70880
SUN PWD CLN W/ FEB
What I'm trying to do is take out the string of number that is at the end of every description if it has a string of digits, but if it has the string of digits at the end its always 5 digits no more or less. I came up with a formula for excel but it doesn't work in access
this is the formula that im using in excel (i also attached a snapshot of the formula)
=IF((IFERROR(((RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ",""))))))-(RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ","")))))))=0,0))=TRUE,(TRIM(LEFT(D10,(LEN(D10)-5)))),(IF(LEN(RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ",""))))))<5,D10,(IF(LEN(RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ",""))))))>5,(LEFT(D10,(LEN(D10)-5))))))))
more simplified
=IF((IFERROR((K10-K10)=0,0))=TRUE,(TRIM(LEFT(D10,(LEN(D10)-5)))),(IF(LEN(K10)<5,D10,(IF(LEN(K10)>5,(LEFT(D10,(LEN(D10)-5))))))))
K10=RIGHT(D10,LEN(D10)-FIND("|",SUBSTITUTE(D10," ","|", LEN(D10)-LEN(SUBSTITUTE(D10," ","")))))
what is does basically it gives me everything after the last space in the string or char for example if I have
SUN PWD CLN & FRESH 70880
k10 will equal to 70880
or if I have
GLAD O/S TLKTC DRSTR78365
k10 will equal to DRSTR78365
or if I have
SUN PWD CLN W/ FEB
k10 will equal FEB
here is where the rest of the formula will come to effect if k10-k10=0 in the fisrt example the whole formula will equal SUN PWD CLN & FRESH, if k10-k10 is an error in the second and third examples then the formula will evaluate is the length of k10 grader then 5 return the whol string of char minus the last five char, but if its less return the whole string of char.
Can anyone help me please come up with the same formula or a different solution for my problem.
Thank you in advance...
