Hi!

English is not my native language and I'm using FileMaker in a other language so some spelling/translation errors might occur.
 

I have searched and tried to find a solution for this but couldn't find any.

 

I have a text field, let's call it "Table::Colors", where users either write values manually or paste values. Most of the time the user paste in values. I want to make a script/calculation that changes the pasted value so it becomes correct.

 

---

 

The user usually paste something like this:

"1Green

 

2Dark blue

 

10Red3

 

15Yell0w

 

Purple4"

 

---

 

I have a script that runs when the user enters/exits the field that looks like this:

"Insert text [Table::Colors; Substitute(Table::Colors;["¶";", "];["1";""];["2";""];["3";""];["4";""];["5";""];["6";""];["7";""];["8";""];["9";""];["0";""])"

 

I also have an auto-enter calculation on the field itself and have set it to replace existing value. It looks like this:
"TextFormatRemove(If(Right(Table::Colors;2) = ", " ; Left (Table::Colors ; Length(Table::Colors) - 2 ) ; Table::Colors ))"

 

When the user exits the field the value now looks like this:

"Green, Dark blue, Red, Yellw, Purple"

 

---

 

However I want it to look like this:

"Green, Dark blue, Red3, Yell0w, Purple4"

 

Basically I want to remove numbers in the beginning of each line but not the numbers that are in the middle or last.

 

Is there a good way to do this?

First thing: a good way would make users enter only one value per field; if there are multiple values to enter, they would be entered into a portal, creating one related record per value.

Next, using both a script and an auto-entered calculation to correct user's entry is not good practice; you want to concentrate the logic in one place and avoid potential duplications and/or contradictions. Here, you will be need to process each line in turn (at least until you change your structure as indicated in my first point). Since you cannot use a recursive custom function (requires the Advanced version), you will have to use a script - so make that script perform all the required corrections.

The script itself could look something like:

Set Variable [ $lines; Value:Substitute ( YourTable::Colors ; [ ", " ; ¶ ] ; [ "," ; ¶ ] ) ]
Loop
  Set Variable [ $i; Value:$i + 1 ]
  Exit Loop If [ $i > ValueCount ( $lines ) ]
  Set Variable [ $cleanLines; Value:Let ( [
  text = Trim ( GetValue ( $lines ; $i ) ) ;
  letters = Substitute ( text ; [ "0" ; "" ] ; [ "1" ; "" ] ; [ "2" ; "" ] ; [ "3" ; "" ] ; [ "4" ; "" ] ; [ "5" ; "" ] ; [ "6" ; "" ] ; [ "7" ; "" ] ; [ "8" ; "" ] ; [ "9" ; "" ] ) ;
  firstLetter = Left ( letters ; 1 ) ;
  pos = Position ( text ; firstLetter ; 1 ; 1 ) ;
  clean = Right ( text ; Length ( text ) - pos + 1 )
  ] ;
  List ( $cleanLines ; clean )
  ) ]
End Loop
Set Field [ YourTable::Colors ; TextFormatRemove ( Substitute ( $cleanLines ; ¶  ; ", " ) ) ]

This will convert an entry of:

1Green
   2Dark blue

10Red3, 45Indigo
15Yell0w
Purple4

to:

Green, Dark blue, Red3, Indigo, Yell0w, Purple4