Jump to content

How to remove numbers in the beginning of words?


This topic is 2804 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
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?
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2804 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.