Extracting data from a field

[Gary1478]

Hi All:

I have a database where I would like field B to extract the first 10 characters from the field A. I would like field C extract characters from 11 to 20 from field A. I would like field D extract characters 21 to 30 from Field A and so on. I have been using the Middle ( A;1;10) calculation then Middle ( A;11;20) then Middle ( A;21;30) but is has not worked precise. Depending on how the data is in the fields it is copying some of the same data in field c as field. Any ideas?

Thanks a bunch..

Gary

[Lee Smith]

Left (YourField; 10)

Middle (YourField; 11; 20)

Middle (YourField; 21; 30)

and so on.

HTH

Lee

[innodes]

May I add a twist to this question?

How do I extract an undetermined number of characters to the left of a specific character? ie., I want to pull the name on the left of the "@" from a list of email addresses.

thanks,

idee

[mr_vodka]

Look into the Position () function.

[Lee Smith]

This topic has come up many times in the past. A search of the Forum should provide an example of the calculation.

There is also a Custom Function Link, and for the data after Here Thanks to our own Fabrice.

HTH

Lee

[innodes]

Thanks for the tips, but here's the problem. I did search and this is the closest I was able to find. I am sure they must be there, but I just couldn't find them.

As for custom calcs, I am a noob, sorry, but I can't figure out how to use them. It looks like that Before calc is what I need, but how do I set it up? Is there a how-to or tutorial of how to use them? The FMP help didn't help much.

[comment]

Why don't you try something simple, like using the Left() function. You say the number of characters is undetermined? Well, use the Position() function to determine it.

[Lee Smith]

Since you own the Advance version, you may as well learn how to use a Custom Function. To start with, I made you a quick sample of the Custom Function mentioned in my earlier post (i.e. Before and After)

Open up the Custom Function Edit Box, by going to

Main Menu >> File >> Manage >> Custom Function

You will see how the Names (click on one), and you will see the Function Parameters, and the Body are shown.

Next, open the

Manage >> Database

and look at the Fields, you note that each Function Parameter has a Field.

Select the "Before" calculation, and you will that the Calculation is a Custom Function. You can see the Custom Functions if you Pull the Drop Down List of Functions (upper Right), and next to the bottom, you will Custom Functions. Highlight that, and you see the two functions used.

HTH

Lee

innodes.fp7.zip

Edited May 20, 2008 by Guest

[innodes]

Thanks Lee,

I'll give it a shot and thank you!

Yes, I do want to learn how to use the custom calcs, but I have been a bit confused so far. The instructions in (F1) help haven't really helped me yet. I will dig through yours and see if I can figure what goes where in inserting a custom calc from the lib you linked. That hopefully will help.

thanks,

mike

Edited May 21, 2008 by Guest

[comment]

I am puzzled: do you want to learn how to install a custom function, then use it without understanding it - or do you want to learn how to extract data from a field?

[innodes]

I want to learn.

I want to learn how to install a custom calc.

I want to understand what it is doing and how it works.

I would like to learn how to someday build my own.

I want to learn more about calcs.

I want to learn more about FMP.

Specifically to this question, I need to learn how to extract specific data. Your earlier suggestion of using the position() looks like it would work. Now I just need to figure out how to build the calc.

Doing what I can, pushing my limits and learning as it comes.

[comment]

Good. You SHOULD know how to install a custom function, but I would never use a custom function that I don't understand. FYI, a significant proportion of the custom functions published on Brian Dunning's site are [not written very well].

Why don't you define a new calculation field using the Position() function to find the position of "@" in the e-mail address (make the result a Number). Then observe the results with a couple of records. You will see that the number of characters you need to extract is equal to the result minus one. So you can define another calculation field, this time using the Left() function, and for the numberOfCharacters parameter use the name of the first calculation field - 1. Make sure the result is Text.

Once you get it working, you can move to the next level: eliminate the first calculation field by nesting the two functions.

[innodes]

I've been out a few days, but thanks. I'll be digging through this and get back once I've worked through it.

appreciate the help.