"Spanning" One Cell to Three using a formula

[DwayneW]

Is it possible to span a value such as:<TABLE style="WIDTH: 105pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=140><COLGROUP><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 105pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 height=17 width=140>
28° 49' 53.976" N
</TD></TR></TBODY></TABLE>

across 3 cells: 28 49 53.976?

I know I can find and replace and delimit the values into multiple cells but I want to have the data split within my worksheet without having to do that.

This is for a GPS converstion sheet I am working on that recieves "DEG MM SS.SSS" data that I am converting into decimal degrees.

I am able to consistently pick out the DEG and MM data using the following commands (after converting symbols to spaces using another cell formula):

=LEFT(N3,FIND(" ",N3))
=LEFT(P3,3) (using the result of the first formula - as th MM column is always 2 digits this works)

My problem is the last SS.SSS part. Sometimes it is 8.68, or 31.985 (varies)... I think I am on the right track... just hoping an excel whiz can give me a pointer.

 

[alx7000]

Hi Dwayne,

Welcome to the board! Here are a couple of solutions (with slightly different results) that seem to work for me:

Excel Workbook
	A	B	C	D
1	28 49' 53.976" N	28	49'	53.976" N
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1))
C1	=LEFT(SUBSTITUTE(A1,B1,""),4)
D1	=SUBSTITUTE(A1,B1&C1,"")

Excel Workbook
	A	B	C	D
1	28 49' 53.976" N	28	49	53.976
Sheet2

Excel 2010

Cell Formulas
Range	Formula
B1	=--LEFT(A1,FIND("°",A1)-1)
C1	=--MID(A1,FIND("°",A1)+2,2)
D1	=--MID(A1,FIND("'",A1)+2,FIND("""",A1)-FIND("'",A1)-2)

Any questions please let me know.

Cheers,
alx7000

 

[DwayneW]

Thanks a million!!! I really appreciate your rapid and excellent solution!

 

[hiker95]

DwayneW,

Excel Workbook
	P	Q	R	S
3	28 49' 53.976" N	28	49	53.976
Sheet1

 

[DwayneW]

The only problem I have run into using the above formula's is when the MM is a single digit or a 0 (which with my data is about 50% of the time)

For example:
<TABLE style="WIDTH: 89pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=119><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 height=17 width=119>122° 6' 41.871" W</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 89pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=119><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 height=17 width=119>122° 0' 58.835" W</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 89pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=119><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 height=17 width=119>98° 7' 0.69" W</TD></TR></TBODY></TABLE>
All the above coordinates return a #VALUE!

Any ideas on compensating for that? Thanks again for your initial responses.

 

[DwayneW]

Actually, using a combination of the two responses I was able to make it work consistently and 100 percent of the time.

Thanks guys!

 

[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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">28° 49' 53.976" N</td><td style="text-align: right;;">28</td><td style="text-align: right;;">49</td><td style="text-align: right;;">53.976</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">122° 6' 41.871" W</td><td style="text-align: right;;">122</td><td style="text-align: right;;">6</td><td style="text-align: right;;">41.871</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">122° 0' 58.835" W</td><td style="text-align: right;;">122</td><td style="text-align: right;;">0</td><td style="text-align: right;;">58.835</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">98° 7' 0.69" W</td><td style="text-align: right;;">98</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0.69</td></tr></tbody></table><p style="width:2.4em;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">Span</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">B1</th><td style="text-align:left">=LOOKUP(<font color="Blue">1E+307,--LEFT(<font color="Red">TRIM(<font color="Green">MID(<font color="Purple">SUBSTITUTE(<font color="Teal">" "&TRIM(<font color="#FF00FF">$A1</font>)," ",REPT(<font color="#FF00FF">" ",50</font>)</font>),50*COLUMNS(<font color="Teal">$B1:B1</font>),50</font>)</font>),{1,2,3,4,5,6,7,8,9}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy across & down...