I have a simple question.

I have an excel sheet with a string of text/numbers

I want to separate each part of the string into individual fields

if the string is:

Item: PK050 - 05 Simple Part 8819GT

the formatting is the same with the exceptions noted

there are approximately 4000 records

I want to separate,

'PK' [sometimes this is three letters]

'050' [sometimes two numbers]

'05 Simple Part'

'8819GT'

I don't need 'Item:'

Thanks in advance

I'm using FMPro 9

can't figure out how to change that in my profile

Go to the Menu item "MY PROFILE » Control Panel » FileMaker Questions

This is not clear enough: does every record contain the exact strings "Item" and "Simple Part", or can there be other expressions in the same position?

They all contain 'Item:', but 'simple part' may be 'generator' or 'hose fitting' or 'fuel pump'

for starters, how would I simply eliminate, 'item:' from the text string?

because the second string is variable in the number of letters/numbers, how would I simply put the next part of the string in a cell?

it may be various combination

PKR03

PR838

UR53

you get the picture I'm sure

Here's a rather simple way (untested):

Element 1 =

Filter ( MiddleWords ( string  ; 2 ; 1 ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

Element 2 =

Filter ( MiddleWords ( string  ; 2 ; 1 ) ; "0123456789" )

 





Element 3 = 

MiddleWords ( string  ; 3 ; WordCount ( string  ) - 3 )

Element 4 =

RightWords ( string  ; 1 )

Thanks

always appreciated!

I have another question.

I have a series of 20 or so fields and ~2.8 million records.

The contents of some fields are in full quotes, some are not.

Is there a script to remove the quotes from, "JEEP" and "2FTRX18W1XCA8548"?

The file is 2 Gb and I can't find a reliable text editor that can open the file. Even so, there are some fields with commas in the contents, so not sure what I'd replace it with as the text editors I've looked at won't let you replace a,

"

with a

tab

Thanks!!

I am assuming you mean the entire content of the field is quoted, not individual parts.

To do this in Filemaker, start with finding the problematic records by searching for:

=="*"

in the relevant field. This is an unindexed search, so it may take a while. Then you can replace the field's contents with a calculation =

Middle ( field ; 2 ; Length ( field ) - 2 )

Make sure you have a backup, as this is not undoable.

To do this in a text editor (e.g. TextWrangler), you could export to a tab-delimited format, and do a series of find/replace:

t" -> t

"t -> t

r" -> r

"r -> r

This will take care of all fields at once, except the first field of the first record.

thank you so much...!!