Apply Formula to Entire Column that Already has Data

[vitamin]

Hi all,

I have a spreadsheet with a column containing URL's.

I'd like to apply a formula to the entire column so that, if a URL has a question mark, delete the question mark and everything right of it.

I found this formula: =LEFT(A1, FIND("?", A1&"?")-1)

Probably a basic question but, how to I apply this formula to an entire column that already has data (in this case the url)???

Thanks!

-Paul

 

[Fluff]

You can't, a cell can contain data or a value, but not both.
You either put the formula in another, or you use a macro.

 

[Fluff]

If you're happy with a macro try

Sub vitamin()
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .Value = Evaluate(Replace("if(@="""","""",left(@,find(""?"",@&""?"")-1))", "@", .Address))
    End With
End Sub

 

[vitamin]

You can't, a cell can contain data or a value, but not both.
You either put the formula in another, or you use a macro.

I see... thanks.

If I make an adjacent blank column, apply the aforementioned formula to the entire column and then paste the URL(s) into that new column, will that do the trick?

As for a macro, I'm 101 and that's a foreign language when it comes to Excel ..

 

[vitamin]

If you're happy with a macro try

Sub vitamin()
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .Value = Evaluate(Replace("if(@="""","""",left(@,find(""?"",@&""?"")-1))", "@", .Address))
    End With
End Sub

Oh, I just saw this code AFTER posting my last comment! I'll learning a bit about macros and give it a try ... thank you.

 

[Fluff]

I see... thanks.

If I make an adjacent blank column, apply the aforementioned formula to the entire column and then paste the URL(s) into that new column, will that do the trick?

Nope fraid not, you could put the URLs into(for instance) col A & the formula in col B, then copy col B & paste as values and delete the other column.

 

[vitamin]

Nope fraid not, you could put the URLs into(for instance) col A & the formula in col B, then copy col B & paste as values and delete the other column.

Interesting...great brain food for thought. I'll try the options, cheers.

 

[Fluff]

You're welcome & thanks for the feedback