Convert image-text hybrids into plain text

morlack · #1 12-02-2014, 02:08 PM

Sometimes when you copy text from a website and paste it in a cell it appears as some kind of image that allows you to select the text contained on it. The thing is, I have an excel sheet with hundreds of these cells and I need to export the file as a CSV, but the cells containing the image-text hybrids won't convert to CSV properly. Is there something I can do to convert all the images to plain text without having to do it manually one by one?

Thanks in advance!

macropod · #2 12-02-2014, 04:45 PM

Without actually seeing the problem worksheet, it can be difficult for anyone to advise on the issue. Can you attach a workbook to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

morlack · #3 12-03-2014, 02:15 PM

Hi macropod, thanks for your prompt response. Here's a workbook example with some of the image-text cells I described.

TIA

macropod · #4 12-03-2014, 03:01 PM

What your workbook has is a bunch of textboxes containing text. Since those textboxes span multiple cells, it's not clear where you want the contents to go. The following macro extracts the contents to the top-left cell spanned by the textbox, then deletes the textbox.

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long
With ThisWorkbook.ActiveSheet
  For i = .Shapes.Count To 1 Step -1
    With .Shapes(i)
      If Not .TextFrame Is Nothing Then
        .TopLeftCell.Value = .TextFrame.Characters.Text
        .Delete
      End If
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub

morlack · #5 12-03-2014, 05:29 PM

Thank you macropod! That macro did exactly what I needed!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Rich text/Plain text Content Controls in Template	michael.fisher5	Word	9	11-19-2014 06:36 AM
Converting plain text to text box/frame	peacespotting	Word	1	08-08-2013 10:39 PM
My plain text post got converted to rich text in a reply, how to convert it back?	david.karr	Outlook	0	01-05-2012 09:46 AM
Getting Plain Text into Excel Columns	binar	Excel	1	11-05-2010 01:46 PM
Replace All with plain text containing subscript	DeaducK	Word	0	06-24-2010 08:16 PM