The longitude and latitude are converted to decimal, And the longitude and latitude are converted to decimal.

The longitude and latitude are converted to decimal, And the longitude and latitude are converted to decimal.

 

I. Basic preparations

1) ISERROR (formula)

Determines whether the formula calculation result is incorrect. If an error occurs, true is returned. Otherwise, false is returned;

= IF (ISERROR (original formula), "", original formula)

2), FIND (find_text, within_text, start_num)

(Https://baike.baidu.com/item/find%E5%87%BD%E6%95%B0/10346224? Fr = aladdin)

Find_text is the string to be searched. Within_text is the cell that contains the keyword to be searched. That is to say, you need to find the keyword Start_num in this cell to specify the number of characters to start searching. For example, if Start_num is 1, the keyword is searched from the first character in the cell. If start_num is ignored, 1 is assumed. 3) left, right, mind (https://jingyan.baidu.com/article/d713063529294513fdf475b1.html)

1. Syntax of the left function: LEFT (text, [num_chars])

   Here, text is the text data source for obtaining the given value. num_chars indicates that several characters need to be extracted from the left, and each character is counted as 1.

   For example, "= LEFT (12345678,3)" indicates to take the first three characters from the character "12345678" and the running result is 123.

2. 2

   Syntax of the right function: RIGHT (text, [num_chars])

   Here, text is the text data source for obtaining the given value. num_chars indicates that several characters need to be extracted from the Right, and each character is counted as 1.

   For example, "= right (12345678,3)" indicates to take the last three characters from the character "12345678" and the running result is 678.

3. 3

   The syntax of the mid function is: MID (text, start_num, num_chars)

   Text is the text data source for obtaining the given value. start_num indicates the number of characters to be extracted from the specified position, and num_chars indicates the number of characters to be extracted from the specified position, each character is counted by 1.

   For example, "= MID (12345678,2, 3)" indicates to take three characters from the second digit of the character "12345678", and the running result is 234.

4. 4

   A combination of several functions.

   For example, if we want to extract the data of the year, month, and day from the "20150812145012" number automatically set by time, the date function is not applicable at this time, and the left, right, and mid functions can be used to achieve this goal.

   Formula:

   = LEFT (A2, 4) & "year" & MID (A2, 6,1) & "month" & MID (A2, 7,2) & "day"

   LEFT (A2, 4) indicates the four digits on the LEFT. MID (A2, 6, 1) indicates the sixth character. MID (A2, 7, 2) indicates two characters starting from the seventh character. The formula is connected to the text with the "&" symbol. The result is

   May August 12, 2015.

 

2. Decimal to latitude and longitude, and decimal to latitude and longitude

Https://jingyan.baidu.com/article/a378c960938851b3282830c3.html)

1. Input data in cells A1 and B1

2. 2

   Decimal to longitude and latitude

   Assume that the raw data is in decimal format. In cell C3, enter publicity = TEXT (INT (A1), "0") & "°" & TEXT (INT (A1-INT (A1) * 60 ), "00") & "'" & TEXT (A1-INT (A1) * 60-INT (A1-INT (A1) * 60) * 60, "00.00 ") &"″"

   The converted longitude value is measured in seconds. The latitude conversion method is the same.

3. 3

   Longitude and latitude to decimal

   Enter the formula in cell E1

   = LEFT (A2, FIND ("°", A2)-1) + MID (A2, FIND ("°", A2) + 1, FIND ("'", A2) -FIND ("°", A2)-1)/60 + MID (A2, FIND ("'", A2) + 1, FIND ("", A2) -FIND ("'", A2)-1)/3600

   The converted longitude decimal value is obtained. The latitude conversion method is the same.

 

 

3. the longitude and latitude values are converted to decimal values, but only the degrees are not in seconds. (Original)

60 hexadecimal: 1 degree = 60 minutes = 3600 seconds

Formula:

= IF (ISERROR (LEFT (C2, FIND ("°", C2)-1) + MID (C2, FIND ("°", C2) + 1, FIND ("'", C2)-FIND ("°", C2)-1)/60 + MID (C2, FIND ("'", C2) + 1, FIND ("", C2)-FIND ("'", C2)-1)/3600), (LEFT (C2, FIND ("°", C2) -1) + MID (C2, FIND ("°", C2) + 1, FIND ("'", C2)-FIND ("°", C2)-1) /60), (LEFT (C2, FIND ("°", C2)-1) + MID (C2, FIND ("°", C2) + 1, FIND ("'", C2)-FIND ("°", C2)-1)/60 + MID (C2, FIND ("'", C2) + 1, FIND ("", C2)-FIND ("'", C2)-1)/3600 ))

Calculation Result:

 

If you want to retain the number of decimal places, right-click to set the cell format:

Numeric value-number of decimal places