Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Remove #ERROR! when field is empty

Topic Labels: Formulas
Solved
Jump to Solution
1084 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Darrell_Miller
4 - Data Explorer
4 - Data Explorer

I have three columns in my table.

Lat/Long Combined - Example: 18.194608875237545, -63.08524490907154
Latitude - Example: 18.194608875237545
Longitude - Example: -63.08524490907154

The formula I’m using in the {Latitude} column is:

IF( FIND(",", {Lat/Long Combined}),
  LEFT({Lat/Long Combined},
    FIND(",", {Lat/Long Combined})-1
  ),
  {Lat/Long Combined}
)

The formula I’m using in the {Longitude} column is:

LEFT(
  RIGHT({Lat/Long Combined},
    LEN({Lat/Long Combined}) - LEN({Latitude}) - 2
  ),
  FIND( ",", 
        RIGHT({Lat/Long Combined},
          LEN({Lat/Long Combined}) - LEN({Latitude}) - 2
        ) & ","
      )-1
)

When there isn’t a value in the {Lat/Long Combined} column, the {Latitude} fields are blank, but the {Longitude} column is populating #ERROR!

Any help would be much appreciated.

Thank you

1 Solution

Accepted Solutions
momentsgoneby80
7 - App Architect
7 - App Architect

Hi and welcome @Darrell_Miller,
in the first formula you use an IF statement. Apply the same IF statement to the second formula as well and the #ERROR messages will dissapear.

IF( FIND(",", {Lat/Long Combined}),
	LEFT(
	  RIGHT({Lat/Long Combined},
		LEN({Lat/Long Combined}) - LEN({Latitude}) - 2
	  ),
	  FIND( ",", 
			RIGHT({Lat/Long Combined},
			  LEN({Lat/Long Combined}) - LEN({Latitude}) - 2
			) & ","
		  )-1
	)
)

See Solution in Thread

2 Replies 2
momentsgoneby80
7 - App Architect
7 - App Architect

Hi and welcome @Darrell_Miller,
in the first formula you use an IF statement. Apply the same IF statement to the second formula as well and the #ERROR messages will dissapear.

IF( FIND(",", {Lat/Long Combined}),
	LEFT(
	  RIGHT({Lat/Long Combined},
		LEN({Lat/Long Combined}) - LEN({Latitude}) - 2
	  ),
	  FIND( ",", 
			RIGHT({Lat/Long Combined},
			  LEN({Lat/Long Combined}) - LEN({Latitude}) - 2
			) & ","
		  )-1
	)
)
Darrell_Miller
4 - Data Explorer
4 - Data Explorer

That did the trick. Thank you!