Split Field

[devinh]

I have a field that has both customer # the customer in one field!

I need to split the two out. simple yes, or so I thought untill I noticed that sometimes they did not enter the customers #.

So I need a calc that will split them. Only grab the first numbers for the customer # and only grap the text for the customer name.

Any Ideas?

Thanks

Devin

[Lee Smith]

How about an example of what the data looks like.

Lee

[devinh]

Some times they put down:

112 KC Power & Light

and some times they put

KC Power & Light

112 is the customer #

The customer is not the same for every record nor is it the same number of digits.

Does this help?

Devin

[mr_vodka]

Devin,

I guess it would depend on how exactly the customer number is structured and Customer Name.

You can use functions such as LeftWords, Right, Filter, GetAsNumber, Position, etc. It all really depends on the data that you have in your fields.

It can be as simple as GetAsNumber to get the account number into a field if its strictly numerical. etc etc...

[Raybaudi]

BTW is customer# always on the left side of the text field ? And is it possible that there is a number into the customer name ?

[devinh]

BTW is customer# always on the left side of the text field ? And is it possible that there is a number into the customer name ?

The customer # is allways on the left side! When it is placed it has a space between number and name.

Yes there is a number is a customer name. I know of one so far.

[mr_vodka]

If this is always true, then for customer Acct number, you can use the LeftWords Function.

LeftWords (Field;1)

For the rest of the account name, you can use.

RightWords ((Field); WordCount(Field)-1)

Edited: Oops forgot to put the logic in there.

Try this for the account number field.

Let ( n=GetAsNumber(LeftWords (Field;1);

Case (Length ( GetAsNumber(n)) = Length (n); c; field)

)

)

and for the account name field

Let ( [ n=GetAsNumber(LeftWords (Field;1); c= RightWords ((Field); WordCount(Field)-1)];



Case (Length ( GetAsNumber(n)) = Length (n); n; "")

)

Edited November 15, 2005 by Guest

[devinh]

Devin,

I guess it would depend on how exactly the customer number is structured and Customer Name.

You can use functions such as LeftWords, Right, Filter, GetAsNumber, Position, etc. It all really depends on the data that you have in your fields.

It can be as simple as GetAsNumber to get the account number into a field if its strictly numerical. etc etc...

The customer number is only numbers. I guess I'm not sure of the filter part.

Here is a calc that I would use to grab the customer name if every record had a customer # placed.

if(wordcount(cust) < 2, cust,rightwords(cust,wordcount(cust)-1))

I guess the part I'm missing is the filter part?

[Raybaudi]

Ok

so

customer# :

[color:red]Case(

Length ( GetAsNumber ( LeftWords ( customerAll ; 1 ) ) ) = Length (LeftWords ( customerAll ; 1 )) ;GetAsNumber ( LeftWords ( customerAll ; 1 ) );

""

)

(the case statment look if the first word is composed of all and only numbers ... as I wish that the first word of a customerName isn't all numbers only )

customerName :

[color:red]Trim ( Substitute ( customerAll ; customer# ; "" ) )

[devinh]

This almost worked! it was able to get the customer # expect for when the customer # was 01. This is not that big of a deal because I need to change that to another #.

Thanks so much!

Devin

[Raybaudi]

Yes, you are right...

So change the calc of customer# to:

Case(

Length ( Filter( LeftWords ( customerAll ; 1 );"0123456789" ) ) = Length (LeftWords ( customerAll ; 1 )) ;LeftWords ( customerAll ; 1 ) ;

""

)

[devinh]

Yeah! that did it!

Thanks again!

Devin