Cut numbers off before a decimal

[Jadegirl]

Hello, I have a column with numbers and I want to cut off the first two before the decimal. The only problem is some have one number before the decimal or no decimal at all. Any thoughts on how to work the Left formula or a better one this way?

<table border="0" cellpadding="0" cellspacing="0" height="246" width="322"><colgroup><col style="width:49pt" width="66"> <col style="mso-width-source:userset;mso-width-alt:4238;width:89pt" width="119"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl101" style="height:14.4pt;width:49pt" height="19" width="66">Column A</td> <td class="xl101" style="width:89pt" width="119">Column B</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl103" style="height:14.4pt" height="19">Data Set</td> <td class="xl101">Desired OUTPUT</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl103" style="height:14.4pt;border-top:none" height="19">9.4</td> <td class="xl104" align="left">9</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl103" style="height:14.4pt;border-top:none" height="19">5.1.2</td> <td class="xl104" align="left">5</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl103" style="height:14.4pt;border-top:none" height="19">5.1.2</td> <td class="xl104" align="left">5</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl102" style="height:14.4pt" height="19">30</td> <td class="xl104" align="left">30</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl102" style="height:14.4pt" height="19">60</td> <td class="xl104" align="left">60</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl102" style="height:14.4pt" height="19">1.1.1</td> <td class="xl104" align="left">1</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl102" style="height:14.4pt" height="19">10.1.2</td> <td class="xl104" align="left">10</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl102" style="height:14.4pt" height="19">12.1</td> <td class="xl104" align="left">12</td> </tr> </tbody></table>

 

[Robert Mika]

Use Text to Columns from Excel Option.
You will end up wit this:

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;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=";">Data Set </td><td style=";">Desired OUTPUT</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">9.4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">5.1.2 </td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">5.1.2 </td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">30</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">60</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">1.1.1 </td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">10.1.2 </td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">12.1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr></tbody></table>

Sheet2​

Then delete columns C and D

 

[Legacy 167133]

Hi Jadegirl,

Check Excel Help on the Truncate Function

 

[Dryver14]

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: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">9.4</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">5.1.2</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">5.1.2</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">30</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">60</TD><TD style="TEXT-ALIGN: right">60</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">1.1.1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">10.1.2</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">12.1</TD><TD style="TEXT-ALIGN: right">12</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; 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>F3</TD><TD>=IFERROR(LEFT(E3,(FIND(".",E3))-1)*1,E3)</TD></TR><TR><TD>F4</TD><TD>=IFERROR(LEFT(E4,(FIND(".",E4))-1)*1,E4)</TD></TR><TR><TD>F5</TD><TD>=IFERROR(LEFT(E5,(FIND(".",E5))-1)*1,E5)</TD></TR><TR><TD>F6</TD><TD>=IFERROR(LEFT(E6,(FIND(".",E6))-1)*1,E6)</TD></TR><TR><TD>F7</TD><TD>=IFERROR(LEFT(E7,(FIND(".",E7))-1)*1,E7)</TD></TR><TR><TD>F8</TD><TD>=IFERROR(LEFT(E8,(FIND(".",E8))-1)*1,E8)</TD></TR><TR><TD>F9</TD><TD>=IFERROR(LEFT(E9,(FIND(".",E9))-1)*1,E9)</TD></TR><TR><TD>F10</TD><TD>=IFERROR(LEFT(E10,(FIND(".",E10))-1)*1,E10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Legacy 167133]

Hi Jadegirl,

sorry but I was wrong. Truncate does not work in your case.

I have also just learnt a valuable lesson from Robert Mika !

Thank you Robert.

 

[DonkeyOte]

If you need formula for sake of being dynamic:

B2:
=LEFT($A2&"",FIND(".",$A2&".")-1)
copied down

 

[Legacy 167133]

IMPRESSIVE DonkeyOte,

You make me feel like an A$$ !!! LOL

 

[Jadegirl]

All of these methods work - THANKS!!!

 

[Jadegirl]

Quick question, what does the -1 mean in the formula:


=LEFT($A2&"",FIND(".",$A2&".")-1)</pre>


-1 </pre>

 

[DonkeyOte]

The function pulls the left n characters from the original string

n is determined by position of first period delimiter in the string less 1

The string is appended with a trailing period so as to ensure a period delimiter is always found.

Given we do not want the period delimiter returned in the final result we subtract 1 from n such that we omit the trailing delimiter.

Where no period delimiter exists we will by default retrieve the entire contents of the original string (null string where original is blank)