Splitting Out Two Numbers

[iainmunro]

Morning

I have a lot of Lat and Long Coordinates that I need to split out into two columns.

I have been relatively successful on a lot of them, but in an amongst my 18000 entries, I have a lot that either have commas in them or the minus is missing from them.

Here is a sample of the data:

As you can see, not all the numbers are the same length, some have commas directly after the number and some have spaces etc

Is there a quick and easy way to resolve this ?

Iain

40.01176, -80.69182
40.026388, -80.696412
37.786128, -80.305247
37.8153, -80.277249
37.818768, -80.289182
37.80523, -80.300014
39.40316, -80.176399
38.435458, -81.852854
39.40267,-79.593484
38.220421,-80.54198
39.25656,-81.553859
38.406401,-82.31429
37.818768,-80.28918
37.868925,-82.17912
38.29905,-82.444152
42.75749 ,-110.9333
44.41403 ,-108.0503
44.35487 ,-106.7049
42.820198,-106.2660
42.826987,-106.3171
42.821196,-106.4024
41.162322,-104.8310
41.160686,-104.8421
41.149008,-104.7794
44.515116,-109.0712

 

[wigi]

Is there a quick and easy way to resolve this ?

Yes, Excel's built-in functionality of Data | Text to columns...

 

[kunal_shrivastava]

Hi iainmunro,

Select the Column, then in Data tab Go to Text To Columns, then select Delimited, then select comma and space as dilimiters and the cilck on NEXT button , then click FINISH.
Hope this will work.

 

[daniels012]

If you want a formula to do it you could do something like:

=LEFT(B3,FIND(",",B3)-1)

=MID(B3,FIND(",",B3)+2,25)

Where your value is in B3

MIchael D

 

[Rick Rothstein]

Just so you know, your example data did not show any missing commas or missing minus signs. Assuming the missing minus signs need to be put in, I think you will need a macro to handle that, so you might as well do everything (splitting the data and filling in the missing minus signs) with a macro. Assuming your data is in Column A of a clean worksheet, this macro will split the data and replace missing minus signs in that second coordinate...

Sub SplitAndFixCoordinates()
  Application.ScreenUpdating = False
  Columns("A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
  Columns("B").Copy Range("C1")
  Columns("C").Replace "-", "=", xlPart
  Range("D1").Value = -1
  Range("D1").Copy
  On Error Resume Next
  Columns("C").SpecialCells(xlCellTypeConstants).Offset(, -1). _
        PasteSpecial xlPasteAll, xlPasteSpecialOperationMultiply
  On Error GoTo 0
  Columns("C:D").Clear
  Application.ScreenUpdating = True
  Range("A1").Select
End Sub

 

[iainmunro]

Many thanks to all that responded, your help was appreciated and it allowed me to complete what I was doing.

Iain

 

[iainmunro]

Evening

I have come across another issue where the above help does not work.

Here is the data:

Columbus OH 43206
Columbus OH 43207
Round Rock TX 78664
Driftwood TX 78619
Austin TX 78702
Austin TX 78704

I have got it going for all the cities, states and zip codes where the city is only one word, but Round Rock, Loa Angeles etc really mess it up.

What is the best way to address this when trying to split them out ?


Iain

 

[Dryver14]

Not sure if Zip codes are always the same lenght, if they are this should work

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 153px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 82px"><COL style="WIDTH: 82px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Columbus OH 43206</TD><TD> </TD><TD> </TD><TD>Columbus </TD><TD>OH 43206</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Columbus OH 43207</TD><TD> </TD><TD> </TD><TD>Columbus </TD><TD>OH 43207</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Round Rock TX 78664</TD><TD> </TD><TD> </TD><TD>Round Rock </TD><TD>TX 78664</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Driftwood TX 78619</TD><TD> </TD><TD> </TD><TD>Driftwood </TD><TD>TX 78619</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Austin TX 78702</TD><TD> </TD><TD> </TD><TD>Austin </TD><TD>TX 78702</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Austin TX 78704</TD><TD> </TD><TD> </TD><TD>Austin </TD><TD>TX 78704</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=LEFT(A1,LEN(A1)-8)</TD></TR><TR><TD>E1</TD><TD>=RIGHT(A1,8)</TD></TR><TR><TD>D2</TD><TD>=LEFT(A2,LEN(A2)-8)</TD></TR><TR><TD>E2</TD><TD>=RIGHT(A2,8)</TD></TR><TR><TD>D3</TD><TD>=LEFT(A3,LEN(A3)-8)</TD></TR><TR><TD>E3</TD><TD>=RIGHT(A3,8)</TD></TR><TR><TD>D4</TD><TD>=LEFT(A4,LEN(A4)-8)</TD></TR><TR><TD>E4</TD><TD>=RIGHT(A4,8)</TD></TR><TR><TD>D5</TD><TD>=LEFT(A5,LEN(A5)-8)</TD></TR><TR><TD>E5</TD><TD>=RIGHT(A5,8)</TD></TR><TR><TD>D6</TD><TD>=LEFT(A6,LEN(A6)-8)</TD></TR><TR><TD>E6</TD><TD>=RIGHT(A6,8)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[iainmunro]

Thanks but it is not giving the intended results.

Amarillo TX 79106
Amarillo TX 79103
Amarillo TX 79104
Amarillo TX 79118
Amarillo T X 79106
Amarillo T X 79101
Memphis T N 38103
Memphis T N 38103
Memphis T N 38103
Memphis T N 38103
Pittsburgh P A 15222
Pittsburgh P A 15222
Pittsburgh P A 15275
Pittsburgh P A 15222
Columbus O H 43206
Columbus O H 43206
Columbus O H 43207
Round Rock T X 78664
Driftwood T X 78619
Austin T X 78702
Austin T X 78704
Austin T X 78704
Chicago I L 60657
Chicago I L 60611
Chicago I L 60607
Chicago I L 60611
Kennesaw G A 30144
Atlanta G A 30308
Atlanta G A 30307
Boston M A 02210
Cambridge M A 02139
Brighton M A 02135
New York N Y 10002
New York N Y 10027
New York N Y 10003
New Orleans L A 70130
Metairie L A 70005

Any ideas ?

Iain

 

[Haseeb Avarakkan]

Hello, Try

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Amarillo TX 79106</td><td style="text-align: right;;"></td><td style=";">Amarillo</td><td style=";">TX</td><td style="text-align: right;;">79106</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Amarillo T X 79101</td><td style="text-align: right;;"></td><td style=";">Amarillo</td><td style=";">T X</td><td style="text-align: right;;">79101</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Memphis T N 38103</td><td style="text-align: right;;"></td><td style=";">Memphis</td><td style=";">T N</td><td style="text-align: right;;">38103</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Pittsburgh P A 15222</td><td style="text-align: right;;"></td><td style=";">Pittsburgh</td><td style=";">P A</td><td style="text-align: right;;">15222</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Columbus O H 43207</td><td style="text-align: right;;"></td><td style=";">Columbus</td><td style=";">O H</td><td style="text-align: right;;">43207</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Round Rock T X 78664</td><td style="text-align: right;;"></td><td style=";">Round Rock</td><td style=";">T X</td><td style="text-align: right;;">78664</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Austin T X 78704</td><td style="text-align: right;;"></td><td style=";">Austin</td><td style=";">T X</td><td style="text-align: right;;">78704</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">Chicago I L 60611</td><td style="text-align: right;;"></td><td style=";">Chicago</td><td style=";">I L</td><td style="text-align: right;;">60611</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">Kennesaw G A 30144</td><td style="text-align: right;;"></td><td style=";">Kennesaw</td><td style=";">G A</td><td style="text-align: right;;">30144</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">Brighton M A 02135</td><td style="text-align: right;;"></td><td style=";">Brighton</td><td style=";">M A</td><td style="text-align: right;;">02135</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";">New York N Y 10003</td><td style="text-align: right;;"></td><td style=";">New York</td><td style=";">N Y</td><td style="text-align: right;;">10003</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">New Orleans L A 70130</td><td style="text-align: right;;"></td><td style=";">New Orleans</td><td style=";">L A</td><td style="text-align: right;;">70130</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";">Metairie L A 70005</td><td style="text-align: right;;"></td><td style=";">Metairie</td><td style=";">L A</td><td style="text-align: right;;">70005</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=TRIM(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">A1,D1,""</font>),E1,""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">TRIM(<font color="Green">SUBSTITUTE(<font color="Purple">A1,E1,""</font>)</font>),3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=RIGHT(<font color="Blue">A1,5</font>)</td></tr></tbody></table></td></tr></table><br />Assume you always have just 5 numbers in the ending.