TRUNCATING FIELDS

[ann1]

Hi Guys
I need a way of stripping out unwanted information contained within a string of data. The data looks similar to this ..

0000000888 Fred Bloggs and Son Limited 100000.00 30 00353 99999999

and all I want to use within that is "Fred Bloggs and Son Limited"

What formula could I use to strip out all the numerical gubbins before and after - bearing in mind that the number of characters of the names will change on each account. Any ideas? Or am I asking the impossible?
Thanks for your help!!

 

[Naith]

Here's a little something you can have totally free of charge:

StringVar OldString;
StringVar NewString;

OldString := "0000000888 Fred Bloggs and Son Limited 100000.00 30 00353 99999999";

While Length(OldString) > 0 Do
(
If Not IsNumeric(Left(OldString,1))
Then
If Left(OldString,1) = '/'
Then NewString := NewString + ' '
Else
If Left(OldString,1) = ','
Then NewString
Else
NewString := NewString + Left(OldString,1);
OldString:= Mid(OldString,2);
);

Replace(NewString,'.','')

All the best,

Naith

 

[ann1]

Thanks Naith

... it works like magic if I type in a string but I need it to do this line by line in a 10000+ row spreadsheet that may have 250+ different account names in it .. that change all the time!

 

[Reebo99]

Try this :

StringVar InputString := "0000000888 Fred Bloggs and Son Limited 100000.00 30 00353 99999999";
NumberVar Counter := 0;
While Not(Counter = Len(InputString)) Do
(Counter := Counter + 1;
If isnumeric(mid(inputstring,Counter,1)) = false then //this checks if it is a numeric character
StringVar OutputString := OutputString&mid(inputstring,Counter,1); //this creates the string
Outputstring := Replace(OutputString,".","" //this removes the decimal points
Trim(OutputString); //this removes the spaces

Replace the italics with your field name.

Let me know how you get on ......

Reebo
Scotland (Sunny with a Smile)

 

[Naith]

Hey Ann,

You're supposed to replace this:

OldString := "0000000888 Fred Bloggs and Son Limited 100000.00 30 00353 99999999";

with this:

OldString := {WhateverYourTableNameIs.WhateverYourFieldNameIs};

Naith

 

[Reebo99]

Or :

StringVar InputString := "0000000888 Fred Bloggs and Son Limited 100000.00 30 00353 99999999";
Inputstring := Replace(InputString,".",""
Inputstring := Replace(InputString,"1",""
Inputstring := Replace(InputString,"2",""
Inputstring := Replace(InputString,"3",""
Inputstring := Replace(InputString,"4",""
Inputstring := Replace(InputString,"5",""
Inputstring := Replace(InputString,"6",""
Inputstring := Replace(InputString,"7",""
Inputstring := Replace(InputString,"8",""
Inputstring := Replace(InputString,"9",""
Inputstring := Replace(InputString,"0",""
Trim(InputString);

There are a few ways to do this....

Reebo
Scotland (Sunny with a Smile)

 

[Reebo99]

Naith,

I think you need to do a Trim() at the end....

Reebo
Scotland (Sunny with a Smile)

 

[Naith]

Yeah, I do. And you need a contract with more work to do.

<N>

 

[ann1]

Reebo and Naith
Thanks for your help guys!

All the suggestions worked a treat (after I had corrected my spreadsheet - doh!).

You rock!!