Extract front numbers

[hardik.chevron]

Hi,
I wanted to do something like this in excel...Can you plz help me on the formula? (extract values before last ".")

146.34.456.23 --> separate 146.34.456
123.45.34.123 --> separate 123.45.34
125.33.456.345 --> separate 125.33.456
-
-
so on...

Thanks,
Hardik

 

[Haseeb Avarakkan]

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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">146.34.456.23</td><td style=";">146.34.456</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">123.45.34.123</td><td style=";">123.45.34</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">125.33.456.345</td><td style=";">125.33.456</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">Sheet3</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">=LEFT(<font color="Blue">A1,FIND(<font color="Red">"|",SUBSTITUTE(<font color="Green">A1,".","|",LEN(<font color="Purple">A1</font>)-LEN(<font color="Purple">SUBSTITUTE(<font color="Teal">A1,".",""</font>)</font>)</font>)</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down...

 

[hardik.chevron]

Thanks! Actually, meantime I did it other way..

=TRIM(LEFT(SUBSTITUTE(G3,"."," ",3),10))

thanks,
Hardik

 

[edokhotnik]

Thanks! Actually, meantime I did it other way..

=TRIM(LEFT(SUBSTITUTE(G3,"."," ",3),10))

thanks,
Hardik

I have similar data, but are IP addresses, where the 2nd octet may have 3 digits. I prefer the following, because it works with any number of digits in the octets:

Excel Workbook
	A	B
1	146.344.456.23	146.344.456
2	123.45.34.123	123.45.34
3	125.33.456.345	125.33.456
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND("|",SUBSTITUTE(A1,".","|",3))-1)
B2	=LEFT(A2,FIND("|",SUBSTITUTE(A2,".","|",3))-1)
B3	=LEFT(A3,FIND("|",SUBSTITUTE(A3,".","|",3))-1)

HTH, Ed