Substituting numbers in a text field

[Scott1981]

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]

Post an example of that the text strings look like.

[comment]

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]

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]

Substitute(Text field1;["0";"X"];["1";"X"];["2";"X"]...["9";"X"])

it will substitute every number valu to "X".

[comment]

:jaw:

[LaRetta]

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]

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]

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]

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]

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]

[although I'd prefer IsEmpty ( char8 )]

Me too !