Apply Formula to Entire Column that Already has Data

vitamin

New Member
Joined
Jun 10, 2014
Messages
10
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
If you're happy with a macro try
Code:
Sub vitamin()
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .Value = Evaluate(Replace("if(@="""","""",left(@,find(""?"",@&""?"")-1))", "@", .Address))
    End With
End Sub
 

vitamin

New Member
Joined
Jun 10, 2014
Messages
10
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

New Member
Joined
Jun 10, 2014
Messages
10
If you're happy with a macro try
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 10, 2014
Messages
10
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,081,575
Messages
5,359,715
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top