How to add particular value in starting of each row of an excel sheet

Question

hello,

i want to ask a question related to MS Excel. I have an excel sheet containing website addresses. The records counts nearly 3000. Now i want to filter it so that duplicates can be removed but the problem is that many web addresses (almost 2000 or so ) in my excel sheet start with httpfor example "http://www.google.com" and the rest starts without http that means some what like this "google" now when i use filter command of excel it removes only duplicates from either same type of data i.e. either with http or without http data. Now the main question : I want to add http in all remaining 1000 or so domains so that i can filter it using filter command, so is there any command to add http in the starting value all 1000 or so rows of sheet? I would be thank full and great full to you if you provide me a perfect solution. Thank you sir.

Kethy Wright

Answer 1

Formula (assuming the addresses are in col A) for B1:

=IF(LEFT(LOWER(A1);7) = "http://"; A1; CONCATENATE("http://"; A1))

Or, if you're in a country that uses commas to separate function arguments:

=IF(LEFT(LOWER(A1), 7) = "http://", A1, CONCATENATE("http://", A1))

Answer 2

Say, if you have data like this

 A                 B
---------        ----------
xyz.com
http://abc.com
yyy.com

In column B, use a function similar to this.

=IF(ISERR(SEARCH("http:",A1,1)),CONCATENATE("http://",A1),A1)

Drag down to fill.

This will put an http prefix for all items in A where the prefix doesn't exist. Tweak this a little bit for your specific case if needed.