Hi John,

Am Wed, 10 Feb 2021 03:48:04 -0800 (PST) schrieb john taiariol:

the coordinates are not always 7 characters.

try:
=IFERROR(IF(LEFT(A1,1)="X",TRIM(SUBSTITUTE(A1,"X", )),TRIM(MID(LEFT(SUBSTITUTE(A1,"F","Z"),FIND("Z",S UBSTITUTE(A1,"F","Z"))-1),FIND("X",LEFT(SUBSTITUTE(A1,"F","Z"),FIND("Z",S UBSTITUTE(A1,"F","Z"))-1))+1,99))),"")

If that doesn't work use an UDF.
Copy following code into a module and call the function in the sheet
with =Coord(A1)

Function Coord(myRange As Range) As String
Set re = CreateObject("vbscript.regexp")
mystring = Replace(myRange.Text, " ", "")
ptrn = "X\d{1,3}\.\d{1,3}"
re.Pattern = ptrn
re.Global = True
re.ignoreCase = False
Set matches = re.Execute(mystring)
If matches.Count 0 Then
Coord = Mid(matches(0), 2)
End If
End Function


Regards
Claus B.
--
Windows10
Microsoft 365 for business