MiddleWords ( (Substitute ( Sym ; "." ; ¶ )) ; 2 ; 1 )

[suzieyoga]

Hunting for Newbie Errors:

I'm importing data (tens of thousands of records) as a text string that looks something like this:

AaAa.4.100

I need this imported string divided into three fields (AaAa, 4, 100). There  are plenty of text tools to do this. In fact, it's simple, provided each part is a known and fixed length. However, the imported data, while in a fairly narrow range, is not fixed.

I need a more generalized approach.

I'm wondering about the MiddleWords function.  I understand what it does -- manipulates lists, which are normally delimited with a paragraph character. This function is exactly what I need if only it would accept "." rather than "paragraph" arguments.

So, I am doing a simple substitution:

MiddleWords ( (Substitute ( Sym ; "." ; ¶ )) ; 2 ; 1 ) -- substituting the paragraph character for the "." character that currently divides my data.

The data output appears fine, but with so many I can only spot check. Does anyone see a problem with this approach? I'm asking because all data goes through this substitution.

Suzie

[Lee Smith]

Why are you using the MiddleWords Function?

Substitute ( Sym ;    [ "." ; ", " ]  )

[Aussie John]

I think the OP wants the make three new fields, not a string. 

For the original question converting the period to a list with substitute is correct. Using getvalue function is the simplest way the separate in their own fields. 

Edited December 23, 2017 by Aussie John

[Lee Smith]

Hi Suzie,

GetValue ( Substitute (  Sym  ; [  "." ; "¶" ]  ); 1 )  // AaAa

GetValue ( Substitute ( Sym ; [  "." ; "¶" ]  ); 2 ) // 4

GetValue ( Substitute ( Sym  ; [  "." ; "¶" ]  ); 3 ) //100

HTH

Lee

[Aussie John]

Btw your middlewords approach should have worked even without the substitution. This does depend on the separator though. I’m not sure, but if the string is seen as a number field and you have periods then it may not see then as separate words. Happy to be corrected on that. 

Edited December 25, 2017 by Aussie John

[Lee Smith]

There is still nothing wrong with.

Leftwords (sym ; 1 )

MiddleWords ( Sym ; 2 ; 1 )

RightWords ( sym : 1 )

if the pattern in sym is consistent with word, word, word,

[Fitch]

On 12/22/2017 at 2:00 PM, suzieyoga said:

I'm wondering about the MiddleWords function.  I understand what it does -- manipulates lists, which are normally delimited with a paragraph character. This function is exactly what I need if only it would accept "." rather than "paragraph" arguments.

MiddleWords is not restricted to paragraph delimiters. You may be thinking of MiddleValues. So if the data has a consistent separator, you don't need to do any substitution, you can simply parse it out as in Lee's last post.

Handy reference: Word Separators