Finding text between two points

[chuckcou]

I have done this before, but it was a long time ago. I should know this, but being a beginner really bites.

Anyway--I want to use a calculation in a field to grab information from another field.

The information I need is ALWAYS between---

and of course that situation is found only one in each record.

[color:green]Example of text---

B. Rudman, Esq.

M. Mayhew

S. Kimmel, Esq.

[color:red]What I need is just the names--

B. Rudman, Esq.

M. Mayhew

S. Kimmel, Esq.

[color:red]What would be the calculation I would use to achive this?

Edited February 19, 2009 by Guest

[comment]

See if this helps:

http://fmforums.com/forum/showpost.php?post/289685/

[chuckcou]

The let function let me put it into a variable and that fine. The problem I am having is how to extract and position correctly.

I think I need something like Position(field; "

;

This is where I start to have trouble.

Edited February 19, 2009 by Guest

[mr_vodka]

No you are mistaking what Michael is stating.

Replace the items in his calc with your criteria.

Where he has "text" is where you are supposed to reference your field.

Where he has prefix is the start point, in your case "

".

Finally suffix is the end point, in your case "

".

Perhaps this modification of his calc can let you better understand it.

Let ( [

text = YourField;

prefix = "

";

suffix = "";

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

end = Position ( text ; suffix ; start ; 1 )

] ;

Middle ( text ; start ; end - start )

)

BTW you can also use substitute to achieve what you want in your specific case.

Substitute ( YourField; [ "

"; "" ]; [ "

"; "" ] ) Edited February 19, 2009 by Guest

[chuckcou]

I will give the let function a try after this post. I remember something like this--

Right(fname;Length(fname) - Position(fname;“

“;Length(fname);-1))

fname=field name

I need to make a correction. I need to come from the end of the line

because I found other

tags within some records.

Edited February 19, 2009 by Guest

[comment]

If you want, you can look for the last occurrence of suffix and extract back to the first preceding prefix:

Let ( [

occurrences = PatternCount ( text ; suffix ) ;

end = Position ( text ; suffix ; 1 ; occurrences ) ;

start = Position ( text ; prefix ; end ; -1 ) + Length ( prefix ) 

] ;

Middle ( text ; start ; end - start )

)

[chuckcou]

uggg. I am close, but still not there

Here is what I have in the field that I am trying to extract from-->

[color:orange]

Commercial distributorship dispute SETTLED:

"He takes a no-nonsense approach, . . . to find a practical solution."

D. Park, Esq.

Current Code--->

[color:green]Right ( html_Data ; Length(html_Data) - Position(html_Data;"

";Length(html_Data);-1))

Results: /p>

[chuckcou]

Thinking about this maybe it not possible because position is moving from the beginning to end. I would need to move from the end back to the 1st /br tag. If I know the posistion of the that br tag then I would be able to extract the name between the
and the

It that the reason for the Let function ?

Edited February 20, 2009 by Guest

[mr_vodka]

The let statement allows for a cleaner version of the calc. YOu dont HAVE to use a let statement.

[mr_vodka]

What's wrong with Michael's calc?

[chuckcou]

What's wrong with Michael's calc?

I have been working with it, but I have to do some learning about let()

In the past I did it with position, middle, left, right. I am just trying to figure those functions out again.

[mr_vodka]

The calc that Michael provided does use the Middle and position functions. PatternCount is used to count the number of times your end characters are used, since you want it from that end point.

Like I said earlier, you do not have to use the LET. It just makes things cleaner since you are setting a calc variable to represent something.

Here is the same calc without using a LET.

Middle ( text ; 

         Position ( text ; prefix ; Position ( text ; suffix ; 1 ; PatternCount ( text ; suffix ) ) ; -1 ) + Length ( prefix ) ; 

         Position ( text ; suffix ; 1 ; PatternCount ( text ; suffix ) ) - Position ( text ; prefix ; Position ( text ; suffix ; 1 ; PatternCount ( text ; suffix ) ) ; -1 ) + Length ( prefix ) 

       )

[comment]

You can do the same thing with Left() and Right() - it's just more hassle because it's a two-step process: first, you need to use Left() to chop off the excess data on the right side; then you must use Right() on the result to remove the unwanted characters on the left. Middle() does it all at once.