Parsing text

[Davesmind]

Please follow with me. I've been trying to get this to work and searching everywhere. Seems no matter what i do i get a different result.

Field1 - This field contains a bunch of data i need to separate. The field will contain one continuous line of numbers separated by only "-". Normally this will be sets of 3 numbers only, but for some reason the data being received will have sections where a 4th "-", is added but this needs to be combined with the 3rd.

Example with 3 numbers: 2.345-1.2345-4.567

Calc1 = 2.345

Calc2 = 1.2345

Calc3= 4.567

Example with 4 numbers: 2.345-1.2345-4.567.-123

Calc1 = 2.345

Calc2 = 1.2345

Calc3 = 4.567.-123

I've tried every combination of Left, Right and Middle, but the end results seem to vary and in some cases omit certain numbers that shouldn't be omitted.

Calc1 I used a simple Left command and Position and pulled the number no problem.

Calc2 I am having problems getting this correctly. I used a Left Position function with a 2nd occurrence. It displays the correct number but also includes the Calc1 result.

Calc3 I have no problems getting the data if its the 3 columns (Right Position Function), if it's 4 then i run into the same problem as Calc2. I figure the easy way is to count how many "-" occur within the text field and then apply one method or another, but Calc3 runs into the same problems with omitting some data and not correctly displaying. Another Problem is that the dash that occurs when there are 4 items must be part of the field.

If anyone can help i would be most appreciated. I don't often use the text parsing abilities of filemaker as i try to control data entry as much as possible, but this data is coming from a client so it's up to me to get the data correctly so we can use it.

[LaRetta]

Why not turn the dashes into carriage returns and then use GetValue ( ) to pull the results? Are you saying sometimes a value is a negative number with a dash?

Please add more specifics about how system will know when to count the dash as part of the number and when not. We need a few more examples. Otherwise, it'll be simple to do.

[comment]

Try:

Calc1 =

Let (

words = Substitute ( Field1 ; "-" ; " " )

;

LeftWords ( words ; 1 )

)

etc.

Calc3 = 4.567.-123

It's hard to see the point of that.

[Davesmind]

Good idea. I usually think of the hardest way of doing things and then find out about an easier approach. The 4 column is an unusual circumstance. It's not a negative number, but it represents a less then whole number and the way it's written tells our employees what it represents. It's sort of a code. All results are text.

Why not turn the dashes into carriage returns and then use GetValue ( ) to pull the results? Are you saying sometimes a value is a negative number with a dash?

Please add more specifics about how system will know when to count the dash as part of the number and when not. We need a few more examples. Otherwise, it'll be simple to do.

That is exactly what i said. I'm the IT guy so my job is to fix it, not understand why they are giving us data with a terrible format.

It's hard to see the point of that.

[comment]

So is there always a period right before the third dash?

[LaRetta]

I was just going to ask that, LOL. You are too fast today!

Let ( values = Substitute ( text ; [ ".-" ; "¶~" ] ; [ "-" ; ¶ ] ; [ "~" ; ".-" ] ) ; GetValue ( values ; 4 ) )

It should work for all values and no matter which value contains the minus - as long as it is formatted as .- or a simple -

Oooops ... you said you didn't need to preserve the dash after all.

UPDATE: I removed a period from the calculation and that makes it:

Let ( values = Substitute ( text ; [ ".-" ; "¶~" ] ; [ "-" ; ¶ ] ; [ "~" ; ".-" ] ) ; GetValue ( values ; 4 ) )

... otherwise it was leaving a period at the end of the third value.

[Davesmind]

So is there always a period right before the third dash?

Apparently so. The basis for the numbers are real estate. Different types of properties are split differently depending on ownership. 1.-15 would represent a specific lot and the owner would have a 15% stake in the property or own a subdivision of the property. Each county, state ect has their own way of displaying this data.

I was able to get everything working with a combination of both of your post. Thank you both for your help.

I was just going to ask that, LOL. You are too fast today!

Let ( values = Substitute ( text ; [ ".-" ; "¶~" ] ; [ "-" ; ¶ ] ; [ "~" ; ".-" ] ) ; GetValue ( values ; 4 ) )

It should work for all values and no matter which value contains the minus - as long as it is formatted as .- or a simple -

Oooops ... you said you didn't need to preserve the dash after all.

UPDATE: I removed a period from the calculation and that makes it:

Let ( values = Substitute ( text ; [ ".-" ; "¶~" ] ; [ "-" ; ¶ ] ; [ "~" ; ".-" ] ) ; GetValue ( values ; 4 ) )

... otherwise it was leaving a period at the end of the third value.

I threw in a check to make sure the 4th value existed if so i then added the 4th GetValue and re-added the "-". Again thank you both for your help.

[LaRetta]

No need, if there isn't a 4th value then one wouldn't display and it won't mess up the other values leaving it in. BTW, if you wish to remove the dash from the 4th number (if it exists) just remove the dash from the final substitute and leave only the period or change it to "" to remove both. I was still unclear on the result for a 4th value since system wouldn't be able to distinguish 1.-15 from a number right before unless a dash preceded. But anyway, I am glad you have it working as you need. :)