Help with parsing string and remove leading and trailing spaces and tabs and line breaks

[tbruce]

Hi all,

Trying to parse the following data in cell A1 and remove leading and trailing spaces, line breaks and tabs that seem to be present in the raw data of the cell. The following formula works to parse the needed data, but the spaces, tab and line breaks are causing me problems after I convert B2 to values (V)123. I just need to parse the latitude and longitude - don't need the Altitude.
Any help would be appreciated.

=(MID(LEFT(A2,FIND("Altitude",A2)-1),FIND("Latitude",A2)+9,LEN(A2)))

Body	Latitude	Longitude
Latitude 037 46 45.304N Longitude 122 25 49.541W Altitude 182 meter (above sea level)	037 46 45.304N

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you are after?

22 03 06.xlsm
	A	B	C
1	Body	Latitude	Longitude
2	Latitude 037 46 45.304N Longitude 122 25 49.541W Altitude 182 meter (above sea level)	037 46 45.304N	122 25 49.541W
Lat Long

Cell Formulas
Range	Formula
B2	B2	=MID(LEFT(A2,FIND(CHAR(10),A2)-1),10,20)
C2	C2	=REPLACE(LEFT(A2,FIND("Altitude",A2)-2),1,FIND("Longitude",A2)+9,"")

 

[jtakw]

Hi,

Another way, B2 formula copied to C2:

Book3.xlsx
	A	B	C
1	Body	Latitude	Longitude
2	Latitude 037 46 45.304N Longitude 122 25 49.541W Altitude 182 meter (above sea level)	037 46 45.304N	122 25 49.541W
Sheet1033

Cell Formulas
Range	Formula
B2:C2	B2	=MID($A2,FIND(B1,$A2)+LEN(B1)+1,14)

 

[jtakw]

Hi again, so now I'm thinking, since I really don't know much about Latitude and Longitude formats, how many different ways it may show in OP's data.
My formula above relies on a fixed length of 14 characters, which I'm thinking may Not be in all cases, so may be these may work, just in case:

Book3.xlsx
	A	B	C
1	Body	Latitude	Longitude
2	Latitude 037 46 45.304N Longitude 122 25 49.541W Altitude 182 meter (above sea level)	037 46 45.304N	122 25 49.541W
Sheet1033

Cell Formulas
Range	Formula
B2	B2	=MID(A2,10,FIND(CHAR(10),A2)-10)
C2	C2	=MID(LEFT(A2,FIND("Altitude",A2)-2),FIND(C1,A2)+10,99)