Jump to content
Sign in to follow this  
Kiele

How do I parse using the day as a sufix

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

How about just =

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Hi comment,

Wow, that is incredible. That is a keeper. :yep:

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

Thank You,

Kiele

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

)

Share this post


Link to post
Share on other sites

Hi fmfunnyguy,

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

Mon Apr 02 2012

Wed May 09 2012

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

Kiele

Share this post


Link to post
Share on other sites

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 by Kiele

Share this post


Link to post
Share on other sites

I have a text field that looks like this. :B

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 )

)

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.