Link to home
Start Free TrialLog in
Avatar of Stephen Roesner
Stephen RoesnerFlag 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.                                  

Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

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
I believe the Power Query functionality in Excel allows you to extract from the right.
Avatar of Stephen Roesner

ASKER

Im sorry I left out the fact that I need to do this in an access query.
Im sorry I left out the fact that I need to do this in an access query.                                  
My mistake.  I should have looked at the question more closely.
ASKER CERTIFIED SOLUTION
Avatar of Maria Barnes
Maria Barnes
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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


Maria i took the 12 down to 10 and it works perfectly thank you so much!!!!!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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:
User generated image

User generated image