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

Why are you using the MiddleWords Function?

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

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, 20178 yr by Aussie John

Hi Suzie,

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

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

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

HTH

Lee

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, 20178 yr by Aussie John

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,

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