Split or delete certain data in excel

[neworchadia]

Hello guys;

I'm having a problem concerning certain data in excel cell, I've cells that contain "country" & "website" items, how can i separate them in two columns using a formula, i've tried the excel help formula with no use, so either deleting certain data from range to range (ex: from: "http" to ".com") or spliting the items will work for me.

And thanks for your support

 

[MrKowz]

Welcome to the forums!

Can you please provide some examples of your data, as well as desired outcome?

 

[Dryver14]

try selecting the cells then using text to colums on the data tab

 

[neworchadia]

Welcome again and thanks for your concern;

I have a table which contains a column of about 3000 rows including in each cell: A description of medical tools and product categories and its country and website, (ex: Product Categories: Haemodialysis Country: Taiwan http://www.*****.com) and i want to delete all the product categories or previous contents before the country and split the country and website in another two columns.

Hope it is useful

Best regards

 

[MrKowz]

Given I understood your conditions properly, try:

Public Sub SplitColA()
Dim i   As Long, _
    LR  As Long, _
    str As String
LR = Range("A" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 1 To LR
    str = Range("A" & i).Value
    Range("B" & i).Value = Mid$(str, InStr(str, "Country") + 9, InStr(str, "http://") - InStr(str, "Country") - 9)
    Range("C" & i).Value = Right$(str, Len(str) - InStr(str, "http://") + 1)
Next i
Application.ScreenUpdating = True
End Sub

 

[neworchadia]

Thanks alot MrKowz

It really worked, you can't imagine my happiness working with the 3000 rows in just one click, just editing manually some cells which don't contain the website and it continues the loop.

Thank you very much, I really appreciate, looking forward to dealing with you in the future