Finding specific text in excel and write a specific value to another cell

Hi all,

I have a text message provider that I use to send text messages to all my customers. If needed I can export a csv file containing specific data about the text messages. I'm interested in tow things from this sheet - one is provided for me - the date of the text message. The other is the sender. This illudes me however as the original value is based on whatever my customer desires. The text message itself however contains the subdomain AND the domain from my customer. This is the value I need (i.e. http//:subdomain.domain).

The only issue is, that this subdomain.domain is placed in different places in the text message, so I can't rely on the usal MID, SEARCH, FIND functions in excel. Sometimes http:// appears 23 characters in, sometimes 59. It varies.

Is there any way to make a generic formula that catches the subdomain.domain, that comes after http:// and is followed by .dk?
I've attached and embedded a sample of the csv file (domains and subdomains removed of course.Experts_exchange_data.xlsx
micambAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Will it always be "http://subdomain.domain/"?

You can use FIND to locate the http://, the find will return the position of the h so will need to add 7 to give the position of the first letter of sub domain. I assume the length of that string is then the issue. However, if the whole string always contains three / characters ie the two with http and then one at the end of the domain string you can use SUBSTITUTE to convert the third / into a unique character eg # and use FIND again to locate that character. Syntax:

=SUBSTITUTE(text, old text, new text, occurrence)

The occurrence is optional and will convert all occurrences of "old text" to "new text" but if you specify that as 3 it will only convert the third occurrence.
0
Rob HensonFinance AnalystCommented:
Combining MID and SUBSTITUTE you get:

=MID(C2,FIND("http",C2,1)+7,FIND("#",SUBSTITUTE(C2,"/","#",3),1)-FIND("http",C2,1)-7)

From cell C2:
'Din sag med sagsnummer 1152 blev opdateret! http://subdomain.domain.dk/followedby moretext….

Open in new window

Gives:  subdomain.domain.dk
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
micambAuthor Commented:
Hi Rob,

Thanks for pitching in!

I get an error when inserting your suggestion... "There is a problem with this fomula (followed by the usual stuff about asking if I'm not trying to type a formula).

Could you insert your formula in the excel sheet provided and assign it to cell C2, C3, C4 etc? And the  upload it? I copy-pasted your code, but it doesn't work on my end.

Best regards
Michael
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob HensonFinance AnalystCommented:
Which column do you need the formulas in?
0
NorieVBA ExpertCommented:
Michael

Try this formula, it worked for me in your workbook.

=MID(TRIM(LEFT(SUBSTITUTE(C2,"dk","dk"&REPT(" ",LEN(C2))),LEN(C2))),SEARCH("http",C2),LEN(C2))
0
micambAuthor Commented:
You can just create a new one and call it user
0
micambAuthor Commented:
Hi Norie,

Same deal with yoru suggestion. Is it a setting in excel I'm missing?
0
Rob HensonFinance AnalystCommented:
0
Rob HensonFinance AnalystCommented:
With the file you uploaded, the cells were formatted as text so entering the formula did not calculate, it just showed the formula string. Is this the issue that you are getting?

If so convert the cells to General format and then paste in the formula.
0
Rob HensonFinance AnalystCommented:
Also, it could be that you should use semi-colon as a separator rather than comma. Try this instead:

=MID(C2;FIND("http";C2;1)+7;FIND("#";SUBSTITUTE(C2;"/";"#";3);1)-FIND("http";C2;1)-7)
0
micambAuthor Commented:
This worked great - I'll just use your sheet going forward! Thanks a bunch!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.