I know there is probably a really simple solution to my problem, I just can't figure it out. Here it is:

Say I have a field populated with "PPPYY-NNNNN". Now say that I want to automatically change the group of three P's to be just one P and the end result to be "PYY-NNNNN". Easy enough with an auto-enter calculation that uses the substitute to find the occurence of three P's and substitute it with one P.

Now, let's say that the number of P's that could be entered into this field by a user is variable. Can't use the Substitute function for that. I was trying to write a recursive custom function to replace any length grouping of P's at the beginning of the text to just one P. I haven't had any luck.

Anyone help out? You can assume that the P's will only ever be grouped at the beginning of the text in the field.

I know there is probably a really simple solution to my problem, I just can't figure it out. Here it is:

Say I have a field populated with "PPPYY-NNNNN". Now say that I want to automatically change the group of three P's to be just one P and the end result to be "PYY-NNNNN". Easy enough with an auto-enter calculation that uses the substitute to find the occurence of three P's and substitute it with one P.

Now, let's say that the number of P's that could be entered into this field by a user is variable. Can't use the Substitute function for that. I was trying to write a recursive custom function to replace any length grouping of P's at the beginning of the text to just one P. I haven't had any luck.

Anyone help out? You can assume that the P's will only ever be grouped at the beginning of the text in the field.

I know there is probably a really simple solution to my problem, I just can't figure it out. Here it is:

Say I have a field populated with "PPPYY-NNNNN". Now say that I want to automatically change the group of three P's to be just one P and the end result to be "PYY-NNNNN". Easy enough with an auto-enter calculation that uses the substitute to find the occurence of three P's and substitute it with one P.

Now, let's say that the number of P's that could be entered into this field by a user is variable. Can't use the Substitute function for that. I was trying to write a recursive custom function to replace any length grouping of P's at the beginning of the text to just one P. I haven't had any luck.

Anyone help out? You can assume that the P's will only ever be grouped at the beginning of the text in the field.

There might be an even easier solution:

There's a pattern of Substitute() that is said to decimate runs of characters to a single one. I haven't checked its validity, but it seems to work:

Substitute ( InputText ;

[ "xxxxxxxxxxxxxxxxxxxxx

There might be an even easier solution:

There's a pattern of Substitute() that is said to decimate runs of characters to a single one. I haven't checked its validity, but it seems to work:

Substitute ( InputText ;

[ "xxxxxxxxxxxxxxxxxxxxx

There might be an even easier solution:

There's a pattern of Substitute() that is said to decimate runs of characters to a single one. I haven't checked its validity, but it seems to work:

Substitute ( InputText ;

[ "xxxxxxxxxxxxxxxxxxxxx

output = Middle ( input ; Position ( input ; "Y" ; 1 ; 1 ) - 1 ; Length ( input ) )

output = Middle ( input ; Position ( input ; "Y" ; 1 ; 1 ) - 1 ; Length ( input ) )

output = Middle ( input ; Position ( input ; "Y" ; 1 ; 1 ) - 1 ; Length ( input ) )

Bikergeek: I'm assuming the "Y" values may change so I don't think that will work but you're on the right track. Looks like the dash is the character to depend on and the dash is a word separator:

output = right(leftwords(input,1),3) & "-" & rightwords(input,1)

Bikergeek: I'm assuming the "Y" values may change so I don't think that will work but you're on the right track. Looks like the dash is the character to depend on and the dash is a word separator:

output = right(leftwords(input,1),3) & "-" & rightwords(input,1)

Bikergeek: I'm assuming the "Y" values may change so I don't think that will work but you're on the right track. Looks like the dash is the character to depend on and the dash is a word separator:

output = right(leftwords(input,1),3) & "-" & rightwords(input,1)

Just in case we are all assuming too much, here's a custom function that does exactly what you asked for:

// Custom Function OneP ( text )

Let (

nextText = Right ( text ; Length ( text ) - 1 ) ;

Case (

Left ( text ; 2 ) = "PP" ; OneP ( nextText ) ; text )

)

Just in case we are all assuming too much, here's a custom function that does exactly what you asked for:

// Custom Function OneP ( text )

Let (

nextText = Right ( text ; Length ( text ) - 1 ) ;

Case (

Left ( text ; 2 ) = "PP" ; OneP ( nextText ) ; text )

)

Just in case we are all assuming too much, here's a custom function that does exactly what you asked for:

// Custom Function OneP ( text )

Let (

nextText = Right ( text ; Length ( text ) - 1 ) ;

Case (

Left ( text ; 2 ) = "PP" ; OneP ( nextText ) ; text )

)

Thanks all! I love getting several different perspectives on a problem. Now I just have to choose which one works best in my situation. I appreciate your time!

Thanks all! I love getting several different perspectives on a problem. Now I just have to choose which one works best in my situation. I appreciate your time!

Thanks all! I love getting several different perspectives on a problem. Now I just have to choose which one works best in my situation. I appreciate your time!