Stephen Roesner

asked on 1/5/2022

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

Last Comment

Mark Edwards

8/22/2022 - Mon

Rainer Jeschor

1/5/2022

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

1/5/2022

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

Stephen Roesner

1/5/2022

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

1/5/2022

ASKER

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

Tom Farrar

1/5/2022

My mistake. I should have looked at the question more closely.

ASKER CERTIFIED SOLUTION

Maria Barnes

1/5/2022

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

1/5/2022

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

1/5/2022

ASKER

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

SOLUTION

Maria Barnes

1/5/2022

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

1/5/2022

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: