Splitting out a Date

[LaRetta]

Hi everyone!

I have a text field which also contains a date. I need to move the date to its own field (ServiceDate) but I'm having trouble pulling it from existing text. I tried using Substitute(Description, " ", "-") to at least separate the description from the date (so I could then pull it easier), but it substitues the three spaces with three dashes I've considered LeftWord, RightWord, Left, Right, and Replace but can't figure out how to handle it. Does anyone have any suggestions?

Existing data is:

W/H 5% LCARE 05/24

MED/ADJ

INS # 67081584 05/30

Pers Check #1751

INS # 2617 07/26

The above data doesn't reflect that there are three spaces between the 'description' and the date. Everything I think of trying, doesn't take into account that the description also has spaces and variable lengths. The date at the end of some fields needs to be moved. Help!!

LaRetta

[Lee Smith]

hi LaRetta,

I'm I missing something obvious here?

What Date?

Existing data is:

W/H 5% LCARE 05/24

MED/ADJ

INS # 67081584 05/30

Pers Check #1751

INS # 2617 07/26

I see what could be three dates, but no years. What date are you referring to, or are you wanting all three dates out?

I think you might be able to pull them out, but I think we need more information on what you want.

Lee

[LaRetta]

Hi Lee,

Each line listed is a separate record. The date I need to pull is at the end, displayed as 5/24 etc. They all represent year 2002. I hope this helps!

LaRetta

[Lee Smith]

You can do this with one calculation with the result of Text, and two will take the text and make it a date.

See sample attached

[Razumovsky]

Try:

Case(Left(Right(Textfield,3),1) = "/", Right(textfield,5) & "/02","")

[LaRetta]

Hi Razumovsky!!

Oh that worked perfectly, thanks so much! Left(Right ? I'm have to study this one for a bit to figure out WHY it worked, however! I appreciate your help very much.

Hi Lee

Well, I'm sure your solution would have worked perfectly also; however, I can't figure out how to open .sit files I appreciate you taking the time to respond though!

LaRetta

[Razumovsky]

Hi L.

Right(Textfield,3) returns the last 3 characters in a text field

Left(Right(Textfield,3),1) returns the first character of the last 3

Lee's idea (I believe) was that you would then want to define a second calc field that was texttodate(firstcalc) to convert the info into true date format.

Keep in mind that this is based on the assumption that all your data is in the format of one of the 5 lines you posted. For example, if line 2 was:

MED/AD

the calc would return ED/AD/02

and if the date was embedded in the text (not at the end) you would need a different calc.

Cheers

[LaRetta]

Oh thanks so much for explaining. Yes, I used TextToDate to then convert it. The formulae was perfect. Is there any way of modifying this calc to then 'remove' that same information from the text field?

LaRetta

[Razumovsky]

I think you would use a second calc field:

Case(Left(Right(textfield,3),1) = "/", Left( textfield , Length(textfield)-6 ), textfield)

Left( textfield , Length(textfield)-6 takes 6 characters off the end of the total length of the textfield (5 for the date, 1 for the space preceeding the date).

Cheers

[Lee Smith]

Hi LaRetta,

I believe that Stuffit makes an expander for windows, if not, I pretty sure there are some free utilities out there that will open them.

In the mean time, Here is a zip file.

Lee

Right.fp5.zip

[LaRetta]

Hi,

I'm concerned that your suggestion will strip text from fields without a date??

Hi Lee! I downloaded your file - thanks much! I had considered your formulae, but Right(Text, 4) & "/2002" also changes the "MED/ADJ" line to "/ADJ/2002".

LaRetta

[Lee Smith]

That's why you need to have the TextToDate.

Lee

[Razumovsky]

Thats why you have the Case(Left(Right(Textfield,3),1) = "/" part. this only triggers the 6 character removal if there is a "/" as the 3rd to last character, presumably a feature unique to your records with dates at the end of the text.

[LaRetta]

BLESS YOU!! That worked great!

LaRetta