Remove #ERROR! when field is empty

[Darrell_Miller]

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

[momentsgoneby80 · Accepted solution]

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]

momentsgoneby80:

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

That did the trick. Thank you!