How do I remove all text after a "-" symbol

[hartmut]

I have an imported web form

I need to retain everything on the right up to a "-" symbol. Everything else needs to be removed.

How to I write that in a text calc?

 

ex.  three blind mice - see how they run

 

would return

 three blind mice

 

Could someone help me?

 

While I am at it I would like to know how to parce the text both ways….

I would like to know how to remove everything up to the "-"

and I would like to know how to remove everything after the "-"

[jbante]

Read in FileMaker's help the documentation on the Position(), Left(), and Right() functions; then let us know if you need any more guidance.

[BrentHedden]

Agreed with Jeremy.  Once you really understand how the Position function works, there is all kinds of (seemingly) tricky text manipulations that you can write.  I prefer to use the Middle function over the Left and Right, but each one has it's strength.

 

FileMaker's text functions are very established and can handle some surprisingly complicated tasks, comparible only to GREP (unix). 

[hartmut]

What I still don't understand is the varying length of the text I want to remove. That text is always different.

[LaRetta]

Ah yes, but the first hyphen is all that needs to be identified.

 

Position ( yourField ; "-" ; 1 ; 1 ) ...

 

tells you where it is.  If you want everything to the left then it would be:

 

Left ( yourField ; Position ( yourField ; "-" ; 1 ; 1 ) - 1 )

 

... meaning everything left of that hyphen (the hyphen's character position less one).

If you want everything to the right then use:

 

Right ( yourField ; Length ( yourField ) - Position ( yourField ; "-" ; 1 ; 1 )  )

 

ADDED:  When pulling text from strings such as this, it is usually best to also wrap it with Trim() in case there is a space.

Edited May 8, 2014 by LaRetta

[Helpful Harry]

Rather than finding just the hyphen*, it's probably better to look for a space-hyphen-space combination

i.e.

Position ( yourField ; " - " ; 1 ; 1 )

That way you won't have shortned text returned if there is a hyphenated word like in "Three deaf-blind mice - see how they run" (which would otherwise only give you "Three deaf").

You may also need to check that it actually finds a hyphen in the text since something like "Three blind mice, see how they run" will cause you problems too.

e.g.

If ( Position ( yourField ; " - " ; 1 ; 1 ) <> 0 ; Left ( yourField ; Position ( yourField ; " - " ; 1 ; 1 ) - 1 ); yourField)

which returns all the text up to the hyphen if there is a hyphen, or returns ALL the text if there is no hyphen.

* Assuming it is just a simple minus sign hyphen and not one of the proper typesetter's longer hyphens ... although I've never tested to see whether or not FileMaker sees them as different.

[David Jondreau]

Harry? From comp.databases.filemaker, that Helpful Harry?

 

You've got 5.5 listed in your profile, so it must be the same!

[Lee Smith]

@ David = I have a feeling that Harry is needing to update his profile? He is also using Panther?

 

@ Harry = here is a link to Update your profile. MY PROFILE

[David Jondreau]

@Lee, not if it's *the* Helpful Harry!

[Fitch]

Another approach would be to replace the hyphen with a carriage return:

Substitute ( "three blind mice - see how they run" ; " - " ; ¶ )

which returns

three blind mice
see how they run

And then it's simply a matter of grabbing each line:

Let( items = Substitute ( yourText ; " - " ; ¶ ) ; 
GetValue ( items ; 1 ) )

This gives you the first line, and you get the second line the same way, with GetValue ( items ; 2 ).

 

Note: if your text already contains carriage returns, this won't be the appropriate technique.

[hartmut]

Thanks so much from everybody. I think  I much better understand it. Something funny happened and I was really crazy for a while

I downloaded a list of terms I wanted to use for this project. After all the answers here I was trying ALL OF THEM and none of them seemed to work.   Finally I checked the font and the actual hyphen was some kind of false hypen.   It must be another character that looks like a hyphen but it wasn't . When I experimented with changing the script to that false HYPHEN which i cut and pasted into the script it worked like a charm. I am glad I found it or I would be a candidate for the ER.. Thanks everyone

[Lee Smith]

... a hyphen but it wasn't ....

This is the reason that we recommend you post a copy of the actual text, not a mockup of it.

 

IMHO, If we had seen it in the beginning, you probably would have had your answer using LaRetta’s calculation.

 

BTW, I don’t see a post where you mentioned that these calculations provided were not working for you, until your last post??

 

Lee

[hartmut]

Thats okay I took a long time learning from everyones efforts. I am very thankful for the help and the achievement I am finally making.

Thank you everyone

[bruceR]

"That's OK"

 

?

 

I think you're missing the point here.

[Nakizimbo]

This is the reason that we recommend you post a copy of the actual text, not a mockup of it.

 

IMHO, If we had seen it in the beginning, you probably would have had your answer using LaRetta’s calculation.

 

BTW, I don’t see a post where you mentioned that these calculations provided were not working for you, until your last post??

 

Lee

Lee, quick question on copying text - how do you copy and paste a series of scripts for inserting into a forum post. I am sure this a simple way but I cannot work it out so had to resort to attaching a screen shot.

[Lee Smith]

Print to Preview (on a mac, not sure what they call it on win) and then copy that and past it into a Reply. If you use the Code <> above when composing your reply, it sometimes makes it better for others to copy.

 

You can also attach a file with the scripts in them?

Edited May 9, 2014 by Lee Smith
changed the wording

[Helpful Harry]

Harry? From comp.databases.filemaker, that Helpful Harry?

You've got 5.5 listed in your profile, so it must be the same!

Yep, the same "Helpful Harry" ... and yep, still using FileMaker 4.1 and 5.5 on a beige G3 PowerMac (under Mac OS 9, although I do use Mac OS X 10.2 for most other things these days). :)