im2bz2p345
Board Regular
- Joined
- Mar 31, 2008
- Messages
- 226
Hello all,
I am trying to extract % discounts on payment terms from Column A in my worksheet. Below is an example of how they look.
In the adjacent column (Column B), I need to input a formula that will keep ONLY the % discount from the text on the left (Column A). If there is a blank space (i.e. A4), just put a 0 in column B (B4). Currently the entire Column B is formated as a percentage with 2 decimal spaces.
I can't seem to figure out how to do this. I was thinking about taking the total length of the cell and subtracting where the % was found, but when I tried using a =FIND("%", A1, 0) to get the location of the percentage sign, I kept getting a #NA message.
Any help would be appreciated!
~ Im2bz2p345![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Sheet1
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 27px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2% 30 Net 31 - ACH</TD></TR><TR style="HEIGHT: 27px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2.25% 30 Net 31 - ACH</TD></TR><TR style="HEIGHT: 27px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2.15% 30 Net 31 - ACH</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">1.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">0.50%</TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I am trying to extract % discounts on payment terms from Column A in my worksheet. Below is an example of how they look.
In the adjacent column (Column B), I need to input a formula that will keep ONLY the % discount from the text on the left (Column A). If there is a blank space (i.e. A4), just put a 0 in column B (B4). Currently the entire Column B is formated as a percentage with 2 decimal spaces.
I can't seem to figure out how to do this. I was thinking about taking the total length of the cell and subtracting where the % was found, but when I tried using a =FIND("%", A1, 0) to get the location of the percentage sign, I kept getting a #NA message.
Any help would be appreciated!
~ Im2bz2p345
Sheet1
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 27px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2% 30 Net 31 - ACH</TD></TR><TR style="HEIGHT: 27px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2.25% 30 Net 31 - ACH</TD></TR><TR style="HEIGHT: 27px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2.15% 30 Net 31 - ACH</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">1.00%</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">0.50%</TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4