Extracting Text

[Ballycroy]

Hi:

I have some data extracted from a web page, the data is paragraph delimited in a text field (much more than listed here)

I want to populate fields, for example: ItemName, StockNumber, EngineType, Status, Options by parsing the data.

I used middleValues function, and that was fine if all the items where in order. But some of the items are missing on some records.

Some sample data:

record 1

Item Name: Acme Engine

Stock Number: 00000000

Engine Type: 8

Status: Pending

Options: None

record 2

Item Name: Acme Engine

Engine Type: 8

Status: Pending

Options: None

record 3

Item Name: Acme Engine

Stock Number: 00000000

Engine Type: 8

Options: None

Case ( PatternCount ( text; "Status:") = 1; Position ( text ; "Status:" ; 1 ; 1 )+7)

That gives me the starting position for extracting the text for "Status:".

But how do I find the end position (paragraph mark at the end of line from starting position)

[Ballycroy]

Should have done a search of the forum using the word "Extract".

Here is a solution that I found from a post by Comment.

Case ( PatternCount ( text ; "Status: " ) ;

Let ( [

start = Position ( text ; "Status: " ; 1 ; 1 ) + 8 ;

end = Position ( text ; ¶ ; start ; 1 )

] ;

Middle ( text ; start ; end - start )

); "")

[comment]

I am glad you found what you need - but I am sure I did not include a default result of "" in the Case() statement - it is 100% redundant.

[Ballycroy]

Quite right Comment, the Let statement was yours, the Case statement with the redundant "" was my addition.

Link to Comment's post:

http://fmforums.com/forum/showtopic.php?tid/172999/post/189829/hl/extract/

Edited April 26, 2007 by Guest