Substituting for sequential parameters

[akaushal]

First time posting so I hope I'm getting it right. I have a complicated spreadsheet for which I'm going to describe a simplified version here, in order to illustrate a challenge I'm having.

Imagine we have rows 1 to 5 and columns A to F

Column D is always some ratio of columns A, B, and C in the same row.

So D1 can be A1/B1 or A1/C1 or B1/A1 or B1/C1 or......etc.

Likewise D2 can be A2/B2 or A2/C2 or.....etc.

Column E displays the formula from column D. I used "FORMULATEXT" or alternatively a VBA I found on this forum called ShowF
<code class="vb keyword">Function</code> <code class="vb plain">ShowF(Rng </code><code class="vb keyword">As</code> <code class="vb plain">Range)</code>
<code class="vb plain">ShowF = Rng.Formula</code>
<code class="vb keyword">End</code> <code class="vb keyword">Function</code>


So the cells in column E are text values like A3/B3 or B15/C15

I'd like to create column F so that I am only getting the ratio without the row numbers included. So I'd like F1 to display A/B or B/C or B/A, etc....

The problem with SUBSTITUTE or REPLACE is that the parameter, like "A3", is in quotes, so it doesn't become "A4" when I drag it down to the next row. It remains "A3" when I try to drag it down to the next row.

Is there some kind of nested function or a VBA script that would be able to achieve this?

Thanks in advance!

 

[Eric W]

Welcome to the forum.

Certainly not pretty, but this formula in F1 should work:

	A	B	C	D	E	F
1	1	1	-2	1	=A1/B1	=A/B
2	2	3	-1	1.5	=B2/A2	=B/A
3	3	5	0	0	=C3/B3	=C/B
4	4	7	1	1.5	=B2/A2	=B/A
5	5	9	2	2.5	=A5/C5	=A/C

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E1 =FORMULATEXT(D1) F1 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Gary's Student]

Consider the following user defined function

Public Function NoNumbers(inpt As String) As String
    Dim i As Long
    
    NoNumbers = ""
    For i = 1 To Len(inpt)
        If Not IsNumeric(Mid(inpt, i, 1)) Then NoNumbers = NoNumbers & Mid(inpt, i, 1)
    Next i
End Function

If a cell contains:

A12/Z657

the function will return:

A/Z

If the cell begins with an equal sign, that will also be retained.

 

[akaushal]

Eric,

Thank you. That's just awesome. I love leveraging simple formulas to get big jobs done. I don't have the "Excel vision" yet to have a mental picture of the nested functions, and I hope to develop that as I get more experience. It would be nice to have that mental roadmap of where I need to go.

I made a mistake in my initial post above. The formula for column D is not ALWAYS a ratio of A,B, and C. Occasionally the value in Column D is one of the first three columns (A, b, or C) divided by an integer. For example, A/10 or B/44.

I think the elegant formula you provided might pull out the occasional integer in the bottom of the fraction.

I know this makes it a more complicated issue. I think the answer falls into one of two categories:

Option 1. Substitute sequential parameters: In row 1 the function substitutes A, B, and C, in place of A1, B1, and C1. And in row 2 the function substitutes A, B, and C, in place of A2, B2, and C2. I couldn't figure out how to do that because the parameters "A1" etc are in quotes, the SUBSTITUTE function won't change "A1" to "A2" when I drag it down.

Option 2. A function that finds the cell identification within a string, and removed the numerical part of it. For example if the value was A1/B1, the function would find "A1" and remove the "1" and then the function would find "B1" and remove the "1". IN the case of A1/44, the function would find "A1" and remove the "1" but leave the "44" alone.

There are ~6000 lines of data so it gets tedious to do it manually.

Thanks again for the quick and friendly response. I thought I had the same solution as you (although I went column-by-column because I can't picture the nesting yet) - and the confounding factor was that some of the values are divided by integers.

Kind Regards,

AK

 

[akaushal]

I was wondering if there was some combination of functions, nesting, or other manipulations I might not be able to picture, that someone with more experience can see the clear pathway.

Thanks again,

AK

 

[Tetra201]

Like this?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1,"A"&ROW(),"A"),"B"&ROW(),"B"),"C"&ROW(),"C"),"=","")

 

[akaushal]

Oh Jee I'm sorry. The answer for A1/10 or B1/44 should be A/10 and B/44. In my primitive option 2 above, the solution would be able to find the "A!" or the "B1" and eliminate the "1"s without messing with the denominator 10 or 44.

 

[akaushal]

Thanks Tetra201. I'm trying your solution. My Excel hung up around 11K lines........let's give it a couple minutes to think things through.

 

[Rick Rothstein]

I believe this UDF will do what you want...

Function NoNumRefs(Cell As Range) As String
  Dim S() As String
  S = Split(Mid(Cell.Formula, 2), "/")
  S(0) = Split(Range(S(0)).Address, "$")(1)
  If S(1) Like "*[A-Za-z]#*" Then S(1) = Split(Range(S(1)).Address, "$")(1)
  NoNumRefs = Join(S, "/")
End Function

Just put the code in a general module (where macro get installed) and then, assuming Eric W's layout from Message #2, put this formula in a cell F1 and copy it down...

=NoNumRefs(E1)

 

[Eric W]

Now here is where it gets a bit sticky. The more exceptions you have, the tougher it is to get a valid formula. I came up with a formula that works, but ONLY if your formulas in E follow this format exactly:

= something / something

The = and / are required, if they are not there, it won't work right. And it only works for 1-letter columns. If you have more complicated formulas, then you'll probably need a UDF like Rick's or Gary's Student.

	A	B	C	D	E	F	G
1	1	1	-2	1	=A1/B1	=A/B	A/B
2	2	3	-1	1.5	=B2/A2	=B/A	B/A
3	3	5	0	0.272727	=B2/11	=B/11	B/11
4	4	7	1	0.25	=C4/A4	=C/A	C/A
5	5	9	2	2.5	=A5/C5	=A/C	A/C

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D1 =A1/B1 D2 =B2/A2 D3 =B2/11 D4 =C4/A4 D5 =A5/C5 E1 =FORMULATEXT(D1) F1 =IF(ISERROR(MID(E1,2,1)+0),"="&MID(E1,2,1)&"/",LEFT(E1,FIND("/",E1)))&IF(ISERROR(MID(E1,FIND("/",E1)+1,1)+0),MID(E1,FIND("/",E1)+1,1),MID(E1,FIND("/",E1)+1,99)) G1 =nonumrefs(D1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Rick, did you mean

=NoNumRefs(D1)

not E1?