Prepend = to every cell in Excel Column to force fraction calculation

Question 1

I have multiple Excel cells in the format 22/7, 355/113, 3927/1250 and so on.

How do I prepend = to each cell so that the actual numeric value is computed?

Using text concatenation as shown in the question: Insert text to the beginning of every Cell in a Column does not work (text is added).

I tried changing cell formatting (numeric,scientific, etc) with no improvement.

Also if A1 is 22/7 then making B1 =VALUE(A1) also does not work, I get #VALUE!

So how to force calculate values of each cell?

Question 2

Say we have data like:

Running this short VBA macro:

Sub MakeEquation()
    Dim N As Long, i As Long

    N = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To N
        With Cells(i, 1)
            .Formula = "=(" & .Value & ")"
        End With
    Next i
End Sub

will produce:

Question 3

If you know how to use Powershell you could of used it to change the cell content.
For values in cells A1 to A3 on sheet1 in an excel spreadsheet called Example.xlsx by using the following script

$FilePath = 'C:UsersUsernameDocumentsExample.xlsx'
# Instantiate the COM object
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($FilePath)
$Sheet = $Excel.WorkSheets.item("sheet1")
$Sheet.activate()

$Sheet.Range('A1:A3').NumberFormat="" 

foreach ($Cell in $Sheet.Range('A1:A3').Cells) {
 $Cell.Value2 = '=' + $Cell.Value2
 }

$WorkBook.Save()
$WorkBook.Close()
$Excel.Quit()

Question 4

The following worksheet function does exactly as you asked:

=LEFT(A1,FIND("/",A1)-1)/RIGHT(A1,LENGTH(A1)-FIND("/";A1))

What it does is find the slash in the fraction and then split the string in the part before the slash and the part behind the slash. It then divides both parts so you get to a decimal.

Question 5

The following is similar to the first one in the link from the comments. It is “cleaner” though, in that the answer given then (2010) was based on aspects of Excel that do not exist anymore.

I am assuming you wish to replace the data with the calculated results. If so, do the following, but if not make the results end up somewhere else that is convenient.

Insert a helper column and enter the following formula (in B1, say):

= "=" & A1

Excel no longer does the abominable thing of considering a leading ' character to be a formatting character like 1-2-3 used to do, but there’s no need to use it anyway as Excel is fine starting a text string with “=”. So this is the simplest thing you can do, really.

Fill or copy/paste down as required. Highlight all of the formula cells and copy. Then go to cell A1 and Paste|Special|Values to place the RESULTS of the above formula (NOT the formulas) “on top of” the starting data. Then delete the helper column. You will now have things like:

=22/7

with it treated as text even though you change the format to a numerical format.

Highlight it all again (or do this immediately after P|S|M and delete the helper column later) and use Find and Replace to search for “=” and replace it with “=”…

Yep, find the equal sign and replace it with itself. Excel is happy to do it and UNLIKE a lot of other techniques, does not keep considering it as text after it does it. If you’d like to always know what the original material was, leave it like this. Examining the cell lets you always see this one, for instance, began with “22/7” because it is still there after the “=”. If you do not need that, highlight them all, copy them, and P|S|V one more time to leave only the results of the calculations in place.

All done.

(By the way, this is one of those situations (oddly, NEVER talked about), not common, but they pop up to surprise us, in which you cannot Paste|Special|Multiply the output of the concatenation by 1 and see it all change to values. It will stay as “=22/7” and to the left edge of the cell like text. You would have to hand change each one with F2-Enter.)

This all said, a nice little macro is the “gold standard.” Quick, easy, you can assign it to a button for use on new material, it’s just all good. So long as you are allowed to use them. But this looks like housekeeping for you, the spreadsheet creator, not something users would be doing after you build the spreadsheet. So you’d think most organizations would be good with that. Gary’s Student’s looks pretty straightforward. Antony’s looks more involved, but seems pretty straightforward too.

And… not to get you in trouble… but you can always add a macro for some housekeeping chore like this while you have the spreadsheet open, use it, then delete it, or just save in the .xlsx format so Excel strips the macro for you. Just sayin’…

From the linked question I alluded to, EVALUATE() would be fun to use. But it’s more trouble as you have to do several things that are slightly harder than the above approach.

I would not recommend the “tear it apart and put it back together” approach that Michthan gives simply because your data might be more involved than literally everything being divided and using the forward slash to show it. If so, now or in the future, you’d find yourself having to expand what that formula looks for to handle all the possible situations. In this situation, maybe not the least problem, but if an easy solution exists that avoids any of that, why not use it instead. That said, that approach will work too and might be necessary depending on how your data might be delivered to you in the future.

(And since these answers are for all to benefit from, someone else may already have a more complex data set and find that approach useful, though fraught with workload getting all the bases covered.)

Leave a Reply Cancel reply