Help with combining formulas

[jdurham67]

I found the solution to many of my issues by searching the board, however I am sure my formulas could be at least cleaned up or that there is a better way to do what I am attempting.

I am starting with a column of numbers that may or may not contain decimal points. I need to remove the decimal point and replace it with a single quote. (I take the results and rename files with a batch process & can't have the decimal before the file extension -ie water_bottle_6.5_oz.jpg has to be water_bottle_6'5_oz.jpg)

I am using a bunch of formulas, in fact, my number starts in col.A and my final answer ends up in col.H. I use LEN, ISERROR & FIND, VALUE, IF and CONCATENATE. Copies of the actual formulas are below.

I guess my main question is: Am I doing this the best way?

CELLA2 original input
COL.B =VALUE(LEN(A2))
COL.C =IF(ISERROR(VALUE(FIND(".",A2))),A2,FIND(".",A2))
COL.D =VALUE(B2-C2)
COL.E =VALUE(LEFT(A2,B2-((B2-C2)+1)))
COL.F =VALUE(RIGHT(A2,(ABS(B2-C2))))
COL.G =IF(E2-F2=0,0,F2)
COL.H =CONCATENATE(E2,"'",G2)

Final outputs 5 = 5'0 5.1 = 5'1 5.135 = 5'135 125.1 = 125'1 etc.

(also, if the staring number is a whole number, the result DOES NOT have to be 5'0 it could just be 5; however that is the only way i could get the final answer to work)

 

[MrKowz]

Welcome to the forums!

Perhaps:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">5</TD><TD>5'0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">5.1</TD><TD>5'1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">5.135</TD><TD>5'135</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">125.1</TD><TD>125'1</TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B1</TH><TD style="TEXT-ALIGN: left">=IF(ISERROR(FIND(".",A1)),A1&"'0",SUBSTITUTE(A1,".","'"))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[CharlesChuckieCharles]

=SUBSTITUTE(LEFT(D19,LEN(D19)-4),".","'")&RIGHT(D19,4) This assumes you want to keep the fullstop by the extension

This might be a bit more robust...
since not all file name extensions are "." + 3 chars anymore

=SUBSTITUTE(LEFT(D19,LEN(D19)-extStart(D19)),".","'")&RIGHT(D19,extStart(D19))


uses a User defined function to target the last fullstop in the string

Public Function extStart(sStr As String) As Long

extStart = (Len(sStr) - InStrRev(sStr, ".")) + 1

End Function