PDA

View Full Version : Concatenate From within formula



Djblois
07-23-2008, 06:49 AM
I need to et the last two digits of a formula into a new cell - just the last two digits. I tried find but it wont look inside a formula, I also tried right. Nothing I can think of works. I don't know if I need to do this in code or with a formula. here is an example of one of the formulas:

=F5*16

and I want to get the 16 from the formula. I want to do this automatically because there are a couple hundred rows and each time the number after the * may be different.

RonMcK
07-23-2008, 09:36 AM
Daniel,

Why not insert a (new) column G, put the value (16) in it, and change the formulat to =if(and(F5>0,G5>0),F5 * G5,"")? You can then copy it down for the range of cells that are popuilated in column F.

HTH,

Djblois
07-23-2008, 11:27 AM
That is the problem - someone else has created the file and hard coded the number into the formula. I need to extract the number from all the formulas in a column of formulas.

RonMcK
07-23-2008, 11:47 AM
Daniel,

A couple of related ways to get the values in column F:

1. inset a column after (to the right of) the column with the formula?

2. Is the number in F5 a value that you enter directly in the cell? If so, enter a '1' for all cells. The value in each cell in the column with your problem formula is the number you are trying to isolate.

3. Now, highlight all the numbers in <problem column letter>, do Edit > Copy (ctl-C) and then Edit > Paste special > Values, pasting the values but not the formulas into the empty column.

OR

4. Start with step 1, then,

5. enter the formula =<column letter>5 / F5 in the empty cell (new column) for row 5 and copy it (or use Edit > Fill > Down) to all the cells in in the rows of the new column that correspond to rows with formulas in <problem column letter>.

6. Now, do step 3, above, doing the Edit > Paste special > Values over the column you are copying from.

7. Result: you have the numbers you are looking to isolate.

Any questions?

HTH,

mdmackillop
07-23-2008, 12:32 PM
A UDF is probably simplest to use. This should work for any operator and length of digit or cell address
=LastBit(A1)


Function LastBit(Data As Range)
Dim i As Long, k As Long, f As String
Dim Res
f = Data.Formula
k = Len(f)
For i = k To 1 Step -1
If Asc(Mid(f, i, 1)) < 48 And Asc(Mid(f, i, 1)) <> 46 Then
Res = Right(f, k - i)
Exit For
End If
Next
If IsNumeric(Res) Then Res = --Res
LastBit = Res
End Function