5 Replies Latest reply on Jan 27, 2017 3:59 PM by lindseym

Calculated Field Produces extra space in result

To star, I found this post but it did not lead me to a solution that was noticeable to me

Nagging Problem in Calculated Field Produces extra space in result

I'm running FMP 14.0.6.

What I am getting as a result is:

101 F

What I expect:

101F

There are some results that have the letter in front of the numbers and those results are as expected.

Here is my calculation:

Let ( [

t = LeftWords ( Location Calculation ; 1 ) ;

x = Filter ( LeftWords ( t ; 1 ) ; "0123456789" )  = LeftWords ( t ; 1 ) ;

l = Length ( LeftWords ( t ; 1 ) ) ;

n = GetAsNumber ( LeftWords ( t ; 1 ) ) ;

Case (

x ;

n & Right ( t ; Length ( t ) - l ) ;

t

)

] ;

// And the second: (substitutes)

Substitute (

\${Copy Side A} ;

[ "XLEVEL#" ; "*" & Level from Space ] ;

[ "LEVEL#" ; Level from Space ] ;

[ "STAIR#" ; Stair from Space ] ;

[ "BLDG#" ; If ( PatternCount ( GetAsText ( Building from Space) ; "RL") > 0 ; "L" ; Building from Space )] ;

[ "LOC#" ; locationCalculationWithoutLeadingZeroes ] ;

// This is the substitution that would need the leading zeros removed

[ ";" ; "¶" ] ;

[ "TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow ) ]

)

)

---------------

And my incoming data is "BLDG#LOC#" So I know there is no space between the two. It doesn't seem to be a space associated before the letter or after the number as I align the fields flush right and left in layout and there doesn't seem to be hidden characters (I don't know how to "show hidden characters" like you would in Adobe Illustrator.

Does anyone see what I am missing?

Thank you Kindly,

-L

I've also added some photos for support.

• 1. Re: Calculated Field Produces extra space in result

Trim (whatever field produced 101)

Trim (whatever field produced F)

I'll bet someone has typed some extra spaces in one or both of those fields.

1 of 1 people found this helpful
• 2. Re: Calculated Field Produces extra space in result

That someone would be me...

I checked the data and it doesn't appear I did any extra characters... so I am not sure how to prevent it in the future, but your fix worked.. so I am happy

Thank you!

-L

Here is my new calculation:

Let ( [

t = LeftWords ( Location Calculation ; 1 ) ;

x = Filter ( LeftWords ( t ; 1 ) ; "0123456789" )  = LeftWords ( t ; 1 ) ;

l = Length ( LeftWords ( t ; 1 ) ) ;

n = GetAsNumber ( LeftWords ( t ; 1 ) ) ;

Case (

x ;

n & Right ( t ; Length ( t ) - l ) ;

t

)

] ;

// And the second: (substitutes)

Substitute (

\${Copy Side A} ;

[ "XLEVEL#" ; "*" & Level from Space ] ;

[ "LEVEL#" ; Level from Space ] ;

[ "STAIR#" ; Stair from Space ] ;

[ "BLDG#" ; If ( PatternCount ( GetAsText ( Trim(Building from Space)) ; "RL") > 0 ; "L" ; Trim(Building from Space ))] ;

[ "LOC#" ; Trim(locationCalculationWithoutLeadingZeroes) ] ;

// This is the substitution that would need the leading zeros removed

[ ";" ; "¶" ] ;

[ "TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow ) ]

)

)

• 3. Re: Calculated Field Produces extra space in result

Here's something I use as an auto enter calc (uncheck "Do Not Replace Existing Value") for those fields like names, phone numbers, cities, etc.:

Trim ( Substitute ( Self ; "¶" ; "" ) )

It removes leading and trailing spaces, carriage returns, etc., that somehow get inadvertently entered.

1 of 1 people found this helpful
• 4. Re: Calculated Field Produces extra space in result

or even:

TextformatRemove ( Trim ( Substitute ( Self ; "¶" ; "" ) ) )

to eliminate any formatting that might get pasted into the field from the clipboard.

And there are custom functions that use filter or substitute to strip out a lot of other non visible characters that can screw up your data if pasted into the field.

1 of 1 people found this helpful
• 5. Re: Calculated Field Produces extra space in result

Thanks

I've a growing list of things to look into. It seems like every time I solve one problem -- I get something else coming up!

Good thing is I am able to fix more and more by myself so learning must be working!