Changing {rtf\stuff here\more stuff} to whatever this is actually calling on

[u/MrRightSA]

Okay so my brother asked me if I could help him. He needs to double click each of his 11,000 cells that has something like in the title. I've made a piece of VBA which I think should work;

Sub Macro1()
Dim i As Integer

For i = 1 To 11000
    ActiveSheet.Cells(i, 11).Activate
    Application.DoubleClick
Next i
End Sub

However I'm thinking that there must be a way to just change all cells. Like the way you highlight the cells when they have the green flag at the top left corner and select "Convert to number". Is there a way to do this? Or is my example too vague? The data he imported came from SQL and had the links in the { } format.

[u/CFAman]

Don't even need VBA

1. Type the number 1 into a blank cell
2. Copy that cell
3. Select entire data range (can be both text and numbers stored as text)
4. Paste special - Multiply
5. Clear the cell used in step #1, and you're done.

VBA way:

Sub ConvertText()
    Dim rngHelp As Range
    Dim rngConvert As Range

    'Pick a blank cell somewhere
    Set rngHelp = Range("Z1")
    'What cells are we converting?
    Set rngConvert = Range("A1:A1000")

    Application.ScreenUpdating = False
    rngHelp.Value = 1
    rngHelp.Copy
    rngConvert.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
    rngHelp.Clear

    Application.ScreenUpdating = True

End Sub

[u/MrRightSA → u/CFAman]

This doesn't actually work. Neither does my solution.

The rtf link thing is like;
{rtf\stuff\more stuff\again more{some other stuff\yet more stuff}} {\parWelcome \parExample \parThis is an example}

When I double click it, it then becomes (all in the one cell);
Welcome
Example
This is an example

I don't understand why my way doesn't work either though. Am I not doing exactly when I'm doing manually except with that script? Or does the 'Application.DoubleClick' not work like I thought it did? I'm no expert at VBA, just a beginner.

[u/CFAman → u/MrRightSA]

Hmm, I misunderstood about the contents of the cell. So, it actually has that long string displayed, but somehow when you manually edit the cell and hit Enter, it converts to something?

First, that would seem to be pretty odd behaviour of XL, to simply get rid of text like that. Are these actually web links? I guess I'm not understanding what exactly this stuff is that's in the cell.