Extract Percentage from Cells

[im2bz2p345]

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

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

 

[sr12345]

=if(a4=0,0,value(left(a4,find("%",a4,1)-1)))

 

[im2bz2p345]

=if(a4=0,0,value(left(a4,find("%",a4,1)-1)))

Thanks for your response sr12345, but unfortunately I get an error with your forumla. See below:

Excel Workbook
	A	B
1	2% 30 Net 31 - ACH	2
2	2.25% 30 Net 31 - ACH	2.25
3	2.15% 30 Net 31 - ACH	2.15
4		0
5	2.00%	#VALUE!
6	1.00%
7	0.50%
Sheet1

Appreciate the help,

~ Im2bz2p345

 

[Scott Huish]

=if(a1="",0,left(a1,find("%",a1))+0)

 

[im2bz2p345]

=if(a1="",0,left(a1,find("%",a1))+0)

Unforunately I still get the same error:

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"><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><TD>B</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><TD style="TEXT-ALIGN: right">0.02</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><TD style="TEXT-ALIGN: right">0.0225</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><TD style="TEXT-ALIGN: right">0.0215</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><TD style="TEXT-ALIGN: right">0</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><TD>#VALUE!</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><TD>#VALUE!</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><TD>#VALUE!</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(A1="",0,LEFT(A1,FIND("%",A1))+0)</TD></TR><TR><TD>B2</TD><TD>=IF(A2="",0,LEFT(A2,FIND("%",A2))+0)</TD></TR><TR><TD>B3</TD><TD>=IF(A3="",0,LEFT(A3,FIND("%",A3))+0)</TD></TR><TR><TD>B4</TD><TD>=IF(A4="",0,LEFT(A4,FIND("%",A4))+0)</TD></TR><TR><TD>B5</TD><TD>=IF(A5="",0,LEFT(A5,FIND("%",A5))+0)</TD></TR><TR><TD>B6</TD><TD>=IF(A6="",0,LEFT(A6,FIND("%",A6))+0)</TD></TR><TR><TD>B7</TD><TD>=IF(A7="",0,LEFT(A7,FIND("%",A7))+0)</TD></TR></TBODY></TABLE></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

Is it because of my formatting? I tried changing the entire column to Text format, but that didn't change anything.

I think I may be getting the #VALUE error in B5-B7 because the FIND is searching for a %, when actually that's just a number which is formated to percentage with 2 decimal places. Not sure.

~ Im2bz2p345

 

[wyeah]

Column A should be formatted as TEXT.

Excel Workbook
	A	B
1	2% 30 Net 31 - ACH	2
2	2.25% 30 Net 31 - ACH	2.25
3	2.15% 30 Net 31 - ACH	2.15
4	*	*
5	2.00%	2.00
6	1.00%	1.00
7	0.50%	0.50
Sheet

Alan

 

[sr12345]

Thanks for your response sr12345, but unfortunately I get an error with your forumla.

Appreciate the help,

~ Im2bz2p345

My guess you are seeing the error because cells A5 through A7 are numbers, not text as the first three were.

Try modifying the equation along the lines of:
=IF(equals zero return zero,if(isnumber(...) then just return the number,recycle the previous text argument)

 

[im2bz2p345]

Column A should be formatted as TEXT.

Excel Workbook
	A	B
1	2% 30 Net 31 - ACH	2
2	2.25% 30 Net 31 - ACH	2.25
3	2.15% 30 Net 31 - ACH	2.15
4	*	*
5	2.00%	2.00
6	1.00%	1.00
7	0.50%	0.50
Sheet

Alan

I changed Column A to Text format. This is what I get when using your forumla:

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"><COL style="WIDTH: 64px"></COLGROUP><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</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><TD style="TEXT-ALIGN: right">2</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><TD style="TEXT-ALIGN: right">2.25</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><TD style="TEXT-ALIGN: right">2.15</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><TD> </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">0.02</TD><TD style="TEXT-ALIGN: right">0.02</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">0.01</TD><TD style="TEXT-ALIGN: right">0.01</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.005</TD><TD style="TEXT-ALIGN: right">0.005</TD></TR></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=TRIM(LEFT(SUBSTITUTE(A1,"%",REPT(" ",100)),100))</TD></TR><TR><TD>B2</TD><TD>=TRIM(LEFT(SUBSTITUTE(A2,"%",REPT(" ",100)),100))</TD></TR><TR><TD>B3</TD><TD>=TRIM(LEFT(SUBSTITUTE(A3,"%",REPT(" ",100)),100))</TD></TR><TR><TD>B4</TD><TD>=TRIM(LEFT(SUBSTITUTE(A4,"%",REPT(" ",100)),100))</TD></TR><TR><TD>B5</TD><TD>=TRIM(LEFT(SUBSTITUTE(A5,"%",REPT(" ",100)),100))</TD></TR><TR><TD>B6</TD><TD>=TRIM(LEFT(SUBSTITUTE(A6,"%",REPT(" ",100)),100))</TD></TR><TR><TD>B7</TD><TD>=TRIM(LEFT(SUBSTITUTE(A7,"%",REPT(" ",100)),100))</TD></TR></TABLE></TD></TR></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

My guess you are seeing the error because cells A5 through A7 are numbers, not text as the first three were.

Try modifying the equation along the lines of:
=IF(equals zero return zero,if(isnumber(...) then just return the number,recycle the previous text argument)

It appears you're correct. When I change the format of Column A to text, A5-A7 become decimals as shown above.

~ Im2bz2p345

 

[im2bz2p345]

Try modifying the equation along the lines of:
=IF(equals zero return zero,if(isnumber(...) then just return the number,recycle the previous text argument)

Using your suggestion, I think that I've got it!

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"><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><TD>B</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><TD style="TEXT-ALIGN: right">0.02</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><TD style="TEXT-ALIGN: right">0.0225</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><TD style="TEXT-ALIGN: right">0.0215</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><TD style="TEXT-ALIGN: right">0</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">0.02</TD><TD style="TEXT-ALIGN: right">0.02</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">0.01</TD><TD style="TEXT-ALIGN: right">0.01</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.005</TD><TD style="TEXT-ALIGN: right">0.005</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(A1="",0,IF(ISNUMBER(A1),VALUE(A1),LEFT(A1,FIND("%",A1))+0))</TD></TR><TR><TD>B2</TD><TD>=IF(A2="",0,IF(ISNUMBER(A2),VALUE(A2),LEFT(A2,FIND("%",A2))+0))</TD></TR><TR><TD>B3</TD><TD>=IF(A3="",0,IF(ISNUMBER(A3),VALUE(A3),LEFT(A3,FIND("%",A3))+0))</TD></TR><TR><TD>B4</TD><TD>=IF(A4="",0,IF(ISNUMBER(A4),VALUE(A4),LEFT(A4,FIND("%",A4))+0))</TD></TR><TR><TD>B5</TD><TD>=IF(A5="",0,IF(ISNUMBER(A5),VALUE(A5),LEFT(A5,FIND("%",A5))+0))</TD></TR><TR><TD>B6</TD><TD>=IF(A6="",0,IF(ISNUMBER(A6),VALUE(A6),LEFT(A6,FIND("%",A6))+0))</TD></TR><TR><TD>B7</TD><TD>=IF(A7="",0,IF(ISNUMBER(A7),VALUE(A7),LEFT(A7,FIND("%",A7))+0))</TD></TR></TBODY></TABLE></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

Thanks for the help everyone! Really appreciate it.

~ Im2bz2p345

 

[Scott Huish]

You shouldn't need the VALUE function.