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

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

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

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

How about just =

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

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

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

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

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)

)

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

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, 201213 yr by Kiele

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 )

)

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!

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