That is strange indeed. It might be tied to your other issue where some have a single quote at the beginning. It sounds like it might have been a csv that went wrong or maybe it contains hidden or invalid characters. Some things to try:
1) Create a calculation in your main file with Length ( LeftWords ( AccountCalc ; 1 ) ). What does it say?
2) Check the field definition Options and Storage. At the bottom, what is the default language?
3) Copy/paste the string into a text editor and reveal all codes and characters.
4) It is like there might be half spaces ... Try wrapping your calculation with TrimAll ( your calc ; 0 ; 0 ). There may be half spaces. I have never worked with a document with half spaces (I'm true Roman, LOL) so I am not sure how FileMaker counts them. If you wrap with TrimAll() then it should remove any half spaces if they exist.
OK I found part of my problem I had a hidden import script bringing in the voo doo. The length from your test revealed length at Ten and English language.
I am still stumped in eliminating the " quotes. I have tried TrimAll ( AccountCode_Imported; 0 ; 0 ) also TrimAll ( AccountCode_Imported; 1 ; 2 ) as well as filtering.
Filter ( AccountCode_Imported ; 1234567890 )
this is the example that I have not been able to trim " YRK5001007 York Creek MXB, Zin Field Blnd 12x750"
Trim ( Substitute ( Yourtextfield ; "\"" ; "" ) )
Should elminate all plain quotes from your text. If you only want to remove this from the first and last characters or have smart quotes, you'll need to use a different expression inside the Substitute function.
TrimAll() will not eliminate the quotes. TrimAll() was suggested to remove half spaces which might have thrown the length off for your export. Your Filter example would also drop the three alpha characters on the front.
To remove the quotes, you need to use: Substitute ( AccountCode_Imported ; "\"" ; "" )
... but the last example you provided has a space after the first quote. Since the data is not necessarily clean, you can easily grab only the account code by using: LeftWords ( AccountCode_Imported ; 1 )
This will drop all word-break characters from either side (so it will remove the beginning quote and the space). This will work for your account code but you still have the rest of the string to parse so the second quote will still need to be removed. What sequence you handle it depends upon what you are trying to accomplish overall.
LeftWords ( AccountCode_Imported ; 1 ) does give me part of the information although in retrospect ideally I would like to take the "LOV" and move it to another field. I receive the information either as; ***LOV*** - CLR3301101 - Calera Pinot Noir Mills - 6x750 or Example 2; . - AJL3401006 - Ajello Furat - 12x750
I created a script using the substitute function to replace the ***LOV*** and realized its important informtation I can use.
How can I extract reather than just remove?
"***LOV*** - CLR3301101 "
I am sorry but I do not understand by this example. Does the text actually say ***LOV*** or does it vary? Where in the string would this exist - only immediately before the account code? Is there additional text before this string? If you can clarify further, we will help you parse it. :-)
This is exactly how it comes in the string in front of the item code. LOV is an indicater for "Last of Vintage"
. - AJL3101010 - Ajello Nero d'Avola - 12x750 . - AJL3401006 - Ajello Furat - 12x750 ***LOV*** - ALH1501399 - ALTENHOFEN RIESLING BEERENAUSL - 12x375 . - APR10042NV - HITORIMUSUME DAIGINJYO SHIZUKU - 6x720 . - APR20035NV - Ichinokura Taru Junmai-Maruaka - 12x500 . - APR40035NV - Meibo Yowanotsuki Ginjo - 12x500 . - APR41047NV - BISHONEN GINJO (6X1.8L) - 6x1.8 . - APR42040NV - Sawanoi Ginjo - 12x720 . - APR43042NV - Shiratake Ginjo Josen - 6x720 . - APR43047NV - Shiratake Ginjo Josen (6x1.8L) - 6x1.8