Avatar of Stephen Roesner
Stephen Roesner
Flag for United States of America asked on

Extracting the right of a string using a special char

In am trying to write a formula to extract the last date from a string of dates in a text field. The data looks like this:

[Touch Date]    (Its a string field)


BLANK

1/1/2021

12/12/2021

1/1/2021;#1/2/2021

12/12/2021;#12/13/2021

1/1/2021;#1/2/2021;#1/3/2021
12/12/2021;#12/13/2021;#12/14/2021 


so I need to grab the last date weather its 8 to 12 chars

ive tried doing 

         Right([Touch Date],InStrRev([Touch Date] ,";#))

but it gives me the last several and not the very last

so for this                         12/12/2021;#12/13/2021;#12/14/2021

it gives me this                ;#12/13/2021;#12/14/2021 

when i want just this       12/14/2021

and it could be single or double digit months and days

and if its blank just return a blank

any ideas?


Im sorry I left out the fact that I need to do this in an access query.                                  

Microsoft Access

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
Rainer Jeschor

Hi,

try to use the Split function and the separator ";#"
Then get the last element of the returned array and check if it is not blank.

Dim CurrentValue As String
Dim ValueArray() As String
Dim possibleValue As String
CurrentValue = yourfieldvalue
ValueArray = Split(CurrentValue,";#")
possibleValue = ValueArray(UBound(ValueArray))

Open in new window

HTH
Rainer
Tom Farrar

I believe the Power Query functionality in Excel allows you to extract from the right.
Stephen Roesner

ASKER
Im sorry I left out the fact that I need to do this in an access query.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Stephen Roesner

ASKER
Im sorry I left out the fact that I need to do this in an access query.                                  
Tom Farrar

My mistake.  I should have looked at the question more closely.
ASKER CERTIFIED SOLUTION
Maria Barnes

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Stephen Roesner

ASKER
Maria,
That is almost a perfect fix. The only issue is when the date is 1/1/2022 then the result is 211/3/2022.
I could put in a bunch of iffs with len if I have to, but any idea on a more elegant fix your so close


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Stephen Roesner

ASKER
Maria i took the 12 down to 10 and it works perfectly thank you so much!!!!!
SOLUTION
Maria Barnes

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Edwards

I'd use the InstrRev (In String Reverse) function to find the position of the last ;#, then use Mid() function to get everything after that.  We have to add conditions if we want to handle nulls and dates where there is no ";#".

Last date: IIf([Touch Date] & ""="",Null,IIf([Touch Date] & ""<>"" And InStr(1,[Touch Date],";#")=0,[Touch Date],Mid([Touch Date],InStrRev([Touch Date],";#")+2)))

Open in new window


Tested: