I need to pull out the right words from a text field. The information in the field is separated by a comma ... sometimes one comma ... other times more than one. I only need the text that follows the last comma.

Example 1: Mary Had A Little Lamb, cassette - sheet music (the calculated result should be: cassette - sheet music)

Example 2: World Is Round, The, VHS (the calculated result should be: VHS)

One way would be...

Let ( [

// Change to a return seperated list

Step1 = Substitute(MyTextField; ","; ¶) ;

// Get the last value in list

Step2 = RightValues(Step1;1);

// Remove trailing Carriage Return

Step3 = Substitute(Step2; ¶; "");

// Trim it up

Step4 = Trim(Step3)

]

; Step4 )

Why do you think 1NF says one fact per field? Anyways here's my stab at it:

Let(rough= yourString ;Replace ( rough ; 1 ; 1+Position ( rough ; ","; 1 ; Length(Filter ( rough ; "," )) ) ; "" ))

What it does, is taking the last comma, and get the text following it!

--sd

WORKS PERFECTLY :-)

WOW

THANK YOU SO MUCH :-)

How about something simple:

Let ( [

len = Length ( text )

] ;

Right ( text ; len - Position ( text ; ", " ; len ; -1 ) - 1 )

)

Edited November 6, 200718 yr by Guest

Indeed much better, I havn't noticed this:

A negative occurrence value causes the scan to go in the opposite direction

Thanks Michael!!

--sd

Actually, I've made a stupid mistake, but the principle is right and I have fixed it now.

Thank you everyone ... I love all the different answers.