Split string lowercase/uppercase challange

[Legacy 143009]

Hi guys!

Well, although I am not very new to excel there are some master tricks that still I don't know. Because of that please tell me step by step like you are explaining to an idiot!

I have a "D" column starts from 5th row and goes up to 165th row. Every cell in this column contains both hotel name and e-mail address. ex. "D5= PARIS HILTON info@parishilton.com"

So it is exactly as it's written, hotel names are in all caps and email addresses are all lows. I think this condition will make something easier but still I couldn't figure out how to...

What I am asking, is there anyway to split the e-mail addresses to "E" column?

On the web I tried many array formulas like "=TRIM......" but it always returned with some errors which means I am doing something wrong.

Long story short, what is the exact formula splits e-mail addresses from D5=>D165 to E5=>165?
How and why?....

Thanks a lot!

 

[Ron Coderre]

With
A1: PARIS HILTON info@parishilton.com

If the string always ends with a space then the email address...try this regular formula:
D165: =TRIM(RIGHT(SUBSTITUTE(D5," ",REPT(" ",LEN(D5))),LEN(D5)))

In that example, the returned value is: info@parishilton.com



Is that something you can work with?

 

[TiaXL]

another way would be to make use of the len() find() and mid() functions to return the appropriate string.


That is assuming that the structure is always NAME_SPACE_EMAIL

 

[Domski]

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100)) will give you the email

and =TRIM(SUBSTITUTE(A1,B1,"")) the name where B1 holds the formula above.

Dom

 

[Legacy 143009]

Can you make an example for me, please? I uploaded a sample sheet. I want to make e-mail address is splitted to E5.

With
A1: PARIS HILTON info@parishilton.com

If the string always ends with a space then the email address...try this regular formula:
D165: =TRIM(RIGHT(SUBSTITUTE(D5," ",REPT(" ",LEN(D5))),LEN(D5)))

In that example, the returned value is: info@parishilton.com



Is that something you can work with?

 

[Domski]

We've both given you examples. All you need to do is change the cell reference.

As for having loaded a sample sheet that's a neat trick as you can't to this forum.

Dom

 

[Legacy 143009]

I asked it for maybe it didn't work?!

We've both given you examples. All you need to do is change the cell reference.

As for having loaded a sample sheet that's a neat trick as you can't to this forum.

Dom

 

[Domski]

I can't see the value in D5 but there's nothing that I can see wrong with the formula.

Dom

 

[Legacy 143009]

A ha, I think I found the defect! First of all thanks for your concern. Actually it's a private company information and I can't share it. Because of that I sent a small sample one. Anyway, the hotel name contains & and * characters. Do you think it can be the problem?

I can't see the value in D5 but there's nothing that I can see wrong with the formula.

Dom

 

[Legacy 143009]

It isn't working even with the simplest sample. I tried both ENTER and SHIFT+CTRL+ENTER.

A ha, I think I found the defect! First of all thanks for your concern. Actually it's a private company information and I can't share it. Because of that I sent a small sample one. Anyway, the hotel name contains & and * characters. Do you thing it can be the problem?