Extract only the numbers, if possible the Lat & Long

[vgomez]

Good day,

Is there a formula that can extract only the numbers form text. The below data is an example of what I get. I would like to just extract only the numbers, really what I only need is the Lat & long numbers. But I could always just filter out the rest of the numbers that are not needed. What I do now is just copy the data and manually delete the rest of the data, sometimes it can be very time consuming. The data I get comes in different forms and lengths, due to the fact that different people are inputting it.

So on the first row I would like to extract only -114.535322,33.703122;-114.535723,33.703225
2nd only -114.535322,33.703122;-114.535723,33.703225
3rd row 34.66788,-116.911;33.91927,-117.121;34.6678750286,-115.910988308;34.919265064,-116.120988073

[TABLE="width: 1187"]
<colgroup><col></colgroup><tbody>[TR]
[TD]BA:River Station :-114.535322,33.703122;-114.535723,33.703225[/TD]
[/TR]
[TR]
[TD]RA1:Phase 1: South River to City Sta.:-115.535708,33.603232;-116.277784,33.68357519[/TD]
[/TR]
[TR]
[TD]Modified Per WOT 125900. AT: Phase 3 (Cat City ), 34.66788,-116.911;33.91927,-117.121(WGG54);34.6678750286,-115.910988308;34.919265064,-116.120988073(NAT53), WOT: 802

Thank you in advance[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Welcome to the forum.

I venture to guess that your problem cannot be solved with forumlas because the data lacks a discernible pattern.

 

[Peter_SSs]

Welcome to the MrExcel board!

You could try this user-defined function (not highly tested). To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function LatLong(s As String) As String
  Static RX As Object
  Dim M As Object
  Dim itm As Variant
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "\-?\d{1,3}\.\d+,\-?\d{1,3}\.\d+;\-?\d{1,3}\.\d+,\-?\d{1,3}\.\d+"
  Set M = RX.Execute(s)
  For Each itm In M
    LatLong = LatLong & ";" & itm
  Next itm
  LatLong = Mid(LatLong, 2)
End Function

Excel Workbook
	A	B
1	BA:River Station :-114.535322,33.703122;-114.535723,33.703225	-114.535322,33.703122;-114.535723,33.703225
2	RA1:Phase 1: South River to City Sta.:-115.535708,33.603232;-116.277784,33.68357519	-115.535708,33.603232;-116.277784,33.68357519
3	Modified Per WOT 125900. AT: Phase 3 (Cat City ), 34.66788,-116.911;33.91927,-117.121(WGG54);34.6678750286,-115.910988308;34.919265064,-116.120988073(NAT53), WOT: 802	34.66788,-116.911;33.91927,-117.121;34.6678750286,-115.910988308;34.919265064,-116.120988073
Sheet1

 

[rahulajmera]

Thank you very much sir!
This has answered my query perfectly.
But it has not solved my problem.

The string data that I'm dealing with is in native language, which is difficult to translate accurately.
The preceding text before the numbers is "चौ.मी"
Now I cant paste that font on to the Visual Basic Window as it is not in English.

How to proceed?

 

[Peter_SSs]

Thank you very much sir!
This has answered my query perfectly.
But it has not solved my problem.

The string data that I'm dealing with is in native language, which is difficult to translate accurately.
The preceding text before the numbers is "चौ.मी"
Now I cant paste that font on to the Visual Basic Window as it is not in English.

How to proceed?

Please stick to your own thread: https://www.mrexcel.com/forum/excel-questions/1064987-selective-sum-numbers-string.html