Extract text from cells?

[delliott777]

Can I extract text from cells... without double-clicking in each cell and copying and pasting?

See attachment.

Thanks,

[davesexcel]

do you want to keep the same values??

[delliott777]

I apologize, I'm not sure exactly what you're asking. I guess the term "value" has thrown me. I'm not sure if "value" is real stuff inside the cell, or the result of the formula.

I want to extract the raw text (formula) from within each cell. If the text (formula) inside a cell is:
=(53+42+55+52+52+54+57+57+56+55+57+51+53+57+46+54+28)/(SUM(Daily!$F$97:$F$113))", then that is exactly what I want to extract out of the cell. The section in red is specificially what I'm after. I need to place those numbers into a database. I'm hoping that there is a more effecient way than double-clicking into each cell, copying & pasting it. I'm needing to extract those numbers from C10:F14. I'm not needing the numbers that the formulas produce. I hope I've explained better than my original post.

Hey, would you mind taking a look another post of mine? I'm seeking feedback at:
http://www.excelforum.com/showthread.php?t=592315

I appreciate your time and willingness to help.

Thanks,
David

[davesexcel]

Open a new workbook

Place your formula

=(53+42+55+52+52+54+57+57+56+55+57+51+53+57+46+54+28 )/(SUM(Daily!$F$97:$F$113))

in A1 and then run this code

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 3/11/2007 by Dave Morrison
Application.DisplayAlerts = False
Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("A1")
    Sheets("Sheet2").Select
    Range("A1").Select

    ActiveCell.Replace What:="=(", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True
ActiveCell = Left(ActiveCell, Len(ActiveCell) - 28)
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="+", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1))
Application.DisplayAlerts = True

End Sub

I am not sure about deleteing

)/(SUM(Daily!$F$97:$F$113))
this example will work, but if you have different sized formulas it might not


Maybe somebody can add to this

ActiveCell = Left(ActiveCell, Len(ActiveCell) - 28)
so it will subtract the actual number of text from ")" to")"

[delliott777]

Dude! It works!!

Thanks!

[Richard Schollar]

As a quick and dirty approach, it might have been a lot simpler to simply convert the formulas to text (eg do a Find/Replace on them, Finding What: = and Replacing With: #=). Then you can copy all the formula text en masse and extract how you like.

Richard