Skip to main content
Solved

Remove #ERROR! when field is empty

  • May 22, 2022
  • 2 replies
  • 25 views

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

Best answer by momentsgoneby80

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
	)
)

2 replies

Forum|alt.badge.img+14

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
	)
)

  • Author
  • New Participant
  • May 24, 2022

That did the trick. Thank you!