Data appears in cells but not in formula bar I converted a PDF to Excel, then wr

Question

Data appears in cells but not in formula bar

I converted a PDF to Excel, then wrote a series of string formulas to extract the data I needed. To help organize the data, I copied the data resulting from those string formulas and pasted them onto a new sheet. I can't recall whether I did a plain "paste" or used "paste values". The data is clearly visible on the sheet, but when I click on any of these cells, the formula bar indicates a blank cell,

My goal is to bring the data into a GIS program and geocode the addresses in the Excel sheet. When the Excel file is brought into the GIS program and I open the file, the columns that don't show in Excel's formula bar are INDEED empty...as I suspected based on the absence of data showing in the formula bar.

It was a lot of work writing some very long formulas to clean up the data - I hope there is a way to salvage the data. It IS there, but NOT there at the same time!!

Thanks for any suggestions. I suspect I messed up the paste operation...

Help !!

Explanation / Answer

Select a bunch of cells that are showing this problem and run the code below and see if that resolves the problem.

If you're not sure how to run the code then do this. Select some cells and then ALT+F11 to open vb editor, right click 'ThisWorkbook' and insert module and paste the code in on the right. While still in vb editor ensure the cursor is within the code and tap F5.

Hi,

Sub CleanMe()
Dim c As Range
    For Each c In Selectiion
    If Not c.HasFormula Then
    With c
        .Value = WorksheetFunction.Trim(.Value)
        .Value = Replace(.Value, Chr(160), "")
    End With
    End If
    Next
End Sub!

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.