How do I parse using the day as a sufix

[Kiele]

I have this text

Receipt-Bel Air Thu Jun 14 2012 $34.61

and this calc

Trim ( Let ( [

text = allReciepts::Description ;

prefix = "Receipt-";

suffic = "Thu" ;

start = Position ( Text ; prefix ; 1 ; 1 ) +

Length ( prefix ) ;

end = Position ( Text ; suffic ; start ; 1 )

] ;

Middle ( Text ; start ; end - start )

))

Returns the correct "Bel Air" but my records can use any day of the week name, first three characters as shown.

I tried the Choose Function, but I can't seem to get it to work.

TIA for your assistance,

Kiele

[comment]

Hm... Would it be correct to assume that the number of words following the portion you want to extract is constant?

[Kiele]

Unfortunately, they very as do the names of the stores, restaurant, etc.

here are a few more examples.

Receipt-Walmart Wed Jun 06 2012 $19.18

Receipt-Carl's Jr Sat Feb 25 2012 $8.90

Receipt-Big Lots Tue Apr 03 2012 $17.20

Receipt-7-ELEVEN Sun Jan 29 2012 $15.76

[Kiele]

Thank YOU comment!

I was totally focus on this the wrong way, and after rereading your hint, I figured it out.

Here is the new calculation. If you spot anything that I missed, let me know please.

Trim (

Let ( [

   text = allReciepts::Description ;

   prefix = "Receipt-" ;

   suffic = RightWords ( text ; 5 ) ;

   start = Position ( Text ; prefix ; 1 ; 1 ) + Length ( prefix ) ;

   end = Position ( Text ; suffic ; start ; 1 )

  ] ;

  Middle ( Text ; start ; end - start )

)

)

Thanks again,

Kiele

[comment]

How about just =

MiddleWords ( Textfield ; 2 ; WordCount ( Textfield ) - 6 )

[fmhappyguy]

Does it work? I would find the position of the day (Mon Tue etc, and then find the position of the $)

In that way you could parse your information into 3 fields (Date, Provider, Amount)

If you need I can write you a custom function to do it.

follow me on twitter! @fmfunnyguy

[Kiele]

Hi comment,

Wow, that is incredible. That is a keeper.

Now I'm off to try and make the dates real dates.

Thank You,

Kiele

[Kiele]

Hi fmfunnyguy,

Yes, it works.

As you can see by my reply, I am needing to parse out the date too. I would appreciate any suggestions or assistance you can send my way.

TIA

Kiele

[fmhappyguy]

I would write a Let function:

Let([

monthNumber= Get the characters 4 to 6 , substite the month name for the number

dayNumber = Get the characters 8 to 9

yearNumber = Get the characters 11 to 14

]

GetAsDate(monthNumber&"/"&dayNumber&"/"&yearNumber)

)

[Kiele]

Hi fmfunnyguy,

I'm sorry, I should have told you I have a text field that looks like this.

Mon Apr 02 2012

Wed May 09 2012

I'm not sure how your suggestion will work with this.

Kiele

[Kiele]

Here is what I came up with:

Let ( [

t = yourField ;

m =

Substitute (

MiddleWords ( t ; 2 ; WordCount ( t ) - 3 ) ;

[ "Jan" ; "01/" ] ; [ "Feb" ; "02/" ] ; [ "Mar" ; "03/" ] ; [ "Apr" ; "04/" ] ;

[ "May" ; "05/" ] ; [ "Jun" ; "06/" ] ; [ "Jul" ; "07/" ] ; [ "Aug" ; "08/" ] ;

[ "Sep" ; "09/" ] ; [ "Oct" ; "10/" ] ; [ "Nov" ; "11/" ] ; [ "Dec" ; "12/" ]

) ;

d = MiddleWords ( t ; 3 ; WordCount ( t ) - 3 ) & "/";

y = RightWords ( t ; 1 )

] ;

m & d & y

)

Kiele

I had an extra "/" and just changed it.

Edited August 1, 2012 by Kiele

[comment]

I have a text field that looks like this.

Mon Apr 02 2012

Wed May 09 2012

Try =

Let ( [

mmm = MiddleWords ( Textfield ; 2 ; 1 ) ;

m = Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; mmm ; 1 ; 1 ) / 3 ) ;

d = MiddleWords ( Textfield ; 3 ; 1 ) ;

y = RightWords ( Textfield ; 1 )

] ;

Date ( m ; d ; y )

)

[fmhappyguy]

Try =

Let ( [

mmm = MiddleWords ( Textfiield ; 2 ; 1 ) ;

m = Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; mmm ; 1 ; 1 ) / 3 ) ;

d = MiddleWords ( Textfiield ; 3 ; 1 ) ;

y = RightWords ( Textfiield ; 1 )

] ;

Date ( m ; d ; y )

)

Nice aproach!

[Kiele]

Thank you comment.

I played with the string "janfebmaraprmayjunjulaugsepoctnovdec" most of the day, but couldn't get it to work. Thanks for the help on this one.

Kiele