Return input in specific form

[jrRaid]

Hi all,

I have a text field that can hold data in the form x and y, where x is digit and y letter.

It can have several different combinations: yyyxxxx, xxxxyyy, xxyyyxx etc.

I need a return in the form:

when there only digits, like xxxx, xx-xx (a dash every 2 digits)

when there is a combination, like yyyxxxx, yyy-xx-xx (letters, dash, 2 digits, dash, 2 digits)

xxxxyyy, xx-xx-yyy

xxyyyxx, xx-yyy-xx

Any hint of possible formula ?

I use already a cf to separate the letters from the digits, but can't have a return when there are only digits...

TIA

[comment]

I don't see how the examples combine into rules - for instance, "xxxyy" should be…??

[jrRaid]

It should separate the letters from the digits, regardless how many letters.

xxxx should return xx-xx

xxxx with any letter at whatever place xx-xx-letter, letter(s)-xx-xx etc.

[Lee Smith]

I have a text field that can hold data in the form x and y, where x is digit and y letter.

xxxxyyy, xx-xx-yyy

xxyyyxx, xx-yyy-xx

Any hint of possible formula ?

I use already a cf to separate the letters from the digits, but can't have a return when there are only digits...

TIA

I think you can do this better using Regular Expressions, However, comment is pretty resourseful.

What happens if you have abcd1234 (ab-cd-12-34?)

Lee

[jrRaid]

As I said, the CF (http://www.briandunning.com/cf/395) will return the letters separated from the digits/numbers.

ABCD-1234, where I need ABCD-12-34

or, when no letters as input data: 12-34

[comment]

You haven't answered my question (regarding an odd number of successive digits).

[jrRaid]

odd numbers of digits in group of 2

x: x

xxx: xx-x

xy: x-y

xxxy: xx-x-y

yyyxxxxx: yyy-xx-xx-x

[comment]

Try something like:

WeirdFormat ( text ) =

Let ( [ 

digits = "0123456789" ; 

len = Length ( text ) ;

num1 = not IsEmpty ( Filter ( Left ( text ; 1 ) ; digits ) ) ; 

num2 =  not IsEmpty ( Filter ( Middle ( text ; 2 ; 1 ) ; digits ) )

] ; 

Case ( 

num1 and num2 ; 

Left ( text ; 2 ) & Case ( len > 2 ; "-" & WeirdFormat ( Right ( text ; len - 2 ) ) ) ;



num1 xor num2 ; 

Left ( text ; 1 ) & Case ( len > 1 ; "-" & WeirdFormat ( Right ( text ; len - 1 ) ) ) ;



Left ( text ; 1 ) & Case ( len > 1 ; WeirdFormat ( Right ( text ; len - 1 ) ) )

)

)

Edited March 7, 2011 by comment

[jrRaid]

Great !!!

Works like a train.

Tx Comment

[jrRaid]

Comment, I'm sorry..

I presented the solution this morning and the power in charge wants to see a group of 5 digits as xx-xxx.

I made already attempts myself, but I'm getting nowhere so far...yet.

Are you willing to tell me what and where I have to change the syntax ?

Thank you.

[comment]

It's not a change of syntax that's required - it's a change of the logic. Right now the function checks the first two characters of text. In order to make this change, it must check four of them - or use a completely different strategy.

[jrRaid]

I want/try to learn something here:

Let ( [ 

digits = "0123456789" ; 

len = Length ( text ) ;

num1 = not IsEmpty ( Filter ( Left ( text ; 1 ) ; digits ) ) ; 

num2 =  not IsEmpty ( Filter ( Middle ( text ; 2 ; 1 ) ; digits ) )

Here you check for 1 or more than 2 digits...



So, if I want to test first to see how many digits I have:

num1 = not IsEmpty ( Filter ( Left ( text ; 1 ) ; digits ) ) ; 

num2 =  not IsEmpty ( Filter ( Middle ( text ; 2 ; 1 ) ; digits ) );

num3 = not IsEmpty ( Filter ( Middle ( text ; 3 ; 2 ) ; digits ));

num4 = not IsEmpty ( Filter ( Middle ( text ; 4 ; 3 ) ; digits ));

num5 = not IsEmpty ( Filter ( Middle ( text ; 5 ; 4 ) ; digits )) 

To return the xx-xxx, I have to to take the Case() from num5 = 1 (test = true) and

Left ( text ; 2 ) & Case ( len > 4 ; "-" & WeirdFormat ( Right ( text ; len - 3 ) ) )

and put that as first line in the result part of the Let().

Am I going somewhere or is it right into a wall?

[comment]

num3 = not IsEmpty ( Filter ( Middle ( text ; 3 ; 2 ) ; digits ))

No, to see if the third character is a digit you need to test for =

not IsEmpty ( Filter ( Middle ( text ; 3 ; 1 ) ; digits ) )

Now, if the first 2 characters are digits, then you also need to test the third and the fourth: because if #3 is a digit and #4 is not, you want to output 3 characters and a hyphen (instead of just 2 and a hyphen as before).

[jrRaid]

Ok, from the beginning (if you don't mind, I never went that deep in calculation/logic)

We need a return form of xx-xxx if we have 5 digits in the field text

We filter the content of field text to only have the digits.

We test if from the filtering the first character is a digit

num1 = not IsEmpty ( Filter ( Left ( text ; 1 ) ; digits ) )

That will return a 1 for true or a 0 for false



We test for the next character

num2 =  not IsEmpty ( Filter ( Middle ( text ; 2 ; 1 ) ; digits ) )

Returns a 1 or 0



Test the next:

not IsEmpty ( Filter ( Middle ( text ; 3 ; 1 ) ; digits ) )

Returns a 1 or 0



Test the next:

not IsEmpty ( Filter ( Middle ( text ; 4 ; 1 ) ; digits ) )

Returns 1 or 0



and, to have a return xx-xxx, where 5 digits are involved, we need to test also for the 5th

not IsEmpty ( Filter ( Middle ( text ; 5 ; 1 ) ; digits ) )

Returns 1 or 0

At this point I understand that we test to see how many digits we have in the 'text'.

What I don't 'see', yet. is how we know if the digits are grouped.

We can have TA12 which will return TA-12 or 1234BA which will return 12-34-BA, which is correct, but I don't see where in your formula you accomplish that.

[comment]

The (old) formula says: if the text begins with two consecutive digits, I output the 2 digits and a hyphen*. If only one of them is a digit, I output the first character and a hyphen*. In any other case, I output the first character and no hyphen.

In every case, the output character is removed from the text, and the rest is sent to the next iteration.

---

(*) if there is more text to follow.

[jrRaid]

Ok, thanks.

Let me chew on that for a while.

I think I have enough info to (maybe/hope so) find a solution by myself now.

Like I said, this is completely new for me.

[Lee Smith]

Question is also being discussed on the Dartmouth list with the subject of: Change a CF.

Lee

W/O giving comment credit for the CF he posted here. :huh:

[comment]

Interesting. It's actually a follow up - and it is depressing to see all those posts and still no resolution of a problem that was left here as "an exercise for the reader"...

[jrRaid]

I have it working now.

Using 3 different custom functions and an additional data field with a script trigger.

The trigger will take the value from the field and determine which CF to use.

It seems to cover all the possible needed outcome.

Tx