Newbies Scott1981 Posted October 16, 2011 Newbies Posted October 16, 2011 I have a text field that contains text verbiage along with 7 digit numbers scattered throughout. I want to find all the 7 digit numbers and convert them to XXXXXXX. Substitute should work if I can define the search text as "all 7 digit numbers." Is that possible to do? Perhaps a custom function. Thanks
Vaughan Posted October 16, 2011 Posted October 16, 2011 Post an example of that the text strings look like.
comment Posted October 16, 2011 Posted October 16, 2011 Substitute should work if I can define the search text as "all 7 digit numbers." That's not a precise enough definition. For example, a 14-digit number could be viewed as two 7-digit numbers - or not. find all the 7 digit numbers and convert them to XXXXXXX. And what would that be good for? See also: http://fmforums.com/forum/topic/67202-extract-7-digits-between/
bcooney Posted October 17, 2011 Posted October 17, 2011 Perhaps you could catch this at the source. What populates this text field? Is it a combination from more than one field and can that be manipulated?
Genelia Posted October 17, 2011 Posted October 17, 2011 Substitute(Text field1;["0";"X"];["1";"X"];["2";"X"]...["9";"X"]) it will substitute every number valu to "X".
LaRetta Posted October 17, 2011 Posted October 17, 2011 The purpose might be to mask important information which shouldn't have been entered into the field for 'all eyes' in the first place. Of course single substitution would replace dates, currency or numbers NOT in this 7-character sequence. I would assume a word delimiter of some sort is between the 7-character strings but as Michael suggests, what if there is a 14-character number? Here is a simple custom function which will do what you ask but you need to decide because this also makes a 14-character number 14 Xs. CF Name: Mask Parameter: text Let ( [ find = Substitute ( text ; ["0";"X"] ; ["1";"X"] ; ["2";"X"] ; ["3";"X"] ; ["4";"X"] ; ["5";"X"] ; ["6";"X"] ; ["7";"X"] ; ["8";"X"] ; ["9";"X"] ) ; pos = Position ( find ; "XXXXXXX" ; 1 ; 1 ) ] ; Left ( text ; pos - 1 ) & Case ( pos ; "XXXXXXX" ) & Case ( PatternCount ( find ; "XXXXXXX" ) ≥ 1 ; Mask ( Right ( text ; Length ( text ) - pos - 6 ) ) ; text ) ) Another way is to identify the string and then substitute the string as we go. This just seemed easier this morning before coffee ... :laugh2:
Raybaudi Posted October 17, 2011 Posted October 17, 2011 If, for a string like this: A12345678B7923450CCC0000000 you need a result like this: AXXXXXXX8BXXXXXXXCCCXXXXXXX you could try: AnotherMask ( text ) Case( not IsEmpty ( text ) ; Let([ s = Left ( text ; 7 ) ; k = KanjiNumeral ( s ) ]; Case( IsEmpty ( Filter ( s ; k ) ) and Length ( k ) = 7 ; "XXXXXXX" & AnotherMask ( Right ( text ; Length ( text ) - 7 ) ) ; Left ( text ; 1 ) & AnotherMask ( Right ( text ; Length ( text ) - 1 ) ) ) ) )
Tom Elliott Posted October 17, 2011 Posted October 17, 2011 I believe the following Custom Function may be what you require, however as others have noted, 14-digit numbers (and indeed any longer number with a multiple of 7 digits) could be treated in one of two ways; this CF does NOT replace 14-digit numbers with "X"s ReplaceSevenDigitNumbers ( theText ) = Let ([ left7 = Left ( theText ; 7 ) ; left14 = Left ( theText ; 14 ) ; char8 = Middle ( theText ; 8 ; 1 ) ; sevenDigits = ( left7 = Filter ( left7 ; "012345x6789" ) ) ; exactlySevenDigits = sevenDigits and ( char8 ≠ Filter ( char8 ; "0123456789" ) ) ; fourteenDigits = ( left14 = Filter ( left14 ; "0123456789" ) ) ] ; Case ( Length ( theText ) < 7 ; theText ; exactlySevenDigits ; "XXXXXXX" & replaceSevenDigitNumbers ( Replace ( theText ; 1 ; 7 ; "" ) ) ; fourteenDigits ; Left ( theText ; 6 ) & replaceSevenDigitNumbers ( Replace ( theText ; 1 ; 6 ; "" ) ) ; sevenDigits ; left7 & ReplaceSevenDigitNumbers ( Replace ( theText ; 1 ; 7 ; "" ) ) ; Left ( theText ; 1 ) & ReplaceSevenDigitNumbers ( Replace ( theText ; 1 ; 1 ; "" ) ) ) ) @LaRetta: Using your CF, any number with 7 or more digits will have its first 7n digits replaced by "X's, where n= Div (number of digits ; 7), e.g. Mask ( "123456789" ) returns "XXXXXXX89" cheers Tom
Raybaudi Posted October 17, 2011 Posted October 17, 2011 I believe the following Custom Function may be what you require Nice, but fails if the field contains exactly seven digits or ends with exactly seven digits [...] exactlySevenDigits = sevenDigits and ( char8 ≠ Filter ( char8 ; "0123456789" ) or char8 = "" ) ; [...]
Tom Elliott Posted October 17, 2011 Posted October 17, 2011 Nice, but fails if the field contains exactly seven digits or ends with exactly seven digits [...] exactlySevenDigits = sevenDigits and ( char8 ≠ Filter ( char8 ; "0123456789" ) or char8 = "" ) ; [...] Thanks for picking that up, and for the correction [although I'd prefer IsEmpty ( char8 )]
Raybaudi Posted October 18, 2011 Posted October 18, 2011 [although I'd prefer IsEmpty ( char8 )] Me too !
Recommended Posts
This topic is 4803 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 accountSign in
Already have an account? Sign in here.
Sign In Now