Calculation field that removes certain text from a text field

[DLM]

Hi guys,

I've FM 5.5v1 running on OS X 10.6.8.

I've a text field titled "Country 1" with many different records, like this:

Italy [ITA] / ITALY - Italian company (active) [iTA33] [Checked]
Spain [ESP] / SPAIN - Spain company [ESP29] [Checked]
France [FRA] / FRANCE - France company [FRA87] [Checked]
etc.

I would like to create a Calculation field that removes everything with the exception of the country name at the start (the red text).

The only common denominator on all records is that there's a space followed by a "[" after each country name (green).

What would the calculation had to be like?

And:

I've another text field titled "Country 2" with many different records, like this:

Italy - Italian company name
Spain - Spain company name
France - France company name
etc.

Here I would also like to create a Calculation field that removes everything with the exception of the country name at the start (the red text).

The only common denominator on all records is that there's a space followed by a "-" after each country name (green).

What would the calculation had to be like?

Best,
 

[LaRetta]

Try:

 

Left ( text ; Position ( text ; "[" ; 1 ; 1 ) - 1 )

 

... then just replace the bracket with the dash for the next one

ooops ... version 5 ... replace semi-colon with comma, I think, unless you are Australian or international.

[doughemi]

You could also use LeftWords(text, 1) for both.

 

EDIT:  Bah, use LaRetta's calc.  Mine will fail for two word countries, such as New Guinea, if they exist in your data.

[DLM]

Hi LaRetta!

 

OK, let me try it out and I'll post result in a few minutes...

 

Best!

Hi doughemi!

 

OK, understood!

 

Best!

[Lee Smith]

Was the Let Calculation available in v 5. ?

[doughemi]

Umm, Lee...Left, not Let (but you're right, Let didn't appear until FM7).

[DLM]

Hi LaRetta! It worked fine!! Thank you!

[Lee Smith]

oops.

 

I have got to get my eyes examined!

 

Actually, I was working with the TimeStamp import for DLM and was trying to remember the Functions in v5. I wish I hadn't upgraded to Mountain Lion, it killed my versions of 6.

 

Anyway, when I read this thread, my mind saw it as Let. Thanks for the correction.