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!

 

[Domski]

& and * won't make any difference and it only needs to be confirmed with Enter as it's not an array formula.

This exact formula works fine for me:

=TRIM(RIGHT(SUBSTITUTE(D5," ",REPT(" ",LEN(D5))),LEN(D5)))

Dom

 

[Legacy 143009]

Ok, didn't work but thank you anyway for spending your time for me.

& and * won't make any difference and it only needs to be confirmed with Enter as it's not an array formula.

This exact formula works fine for me:

=TRIM(RIGHT(SUBSTITUTE(D5," ",REPT(" ",LEN(D5))),LEN(D5)))

Dom

 

[Legacy 143009]

Anybody idea?!

 

[Ingolf]

Hi,

try change "," with ";" in formula =TRIM(RIGHT(SUBSTITUTE(D5," ",REPT(" ",LEN(D5))),LEN(D5)))
maybe your regional settings...

 

[Rick Rothstein]

try change "," with ";" in formula =TRIM(RIGHT(SUBSTITUTE(D5," ",REPT(" ",LEN(D5))),LEN(D5)))
maybe your regional settings...

If Ingolf's suggestion doesn't solve your problem, then perhaps you have a blank space at the end of the text in the cell? If that is the case, then this formula should work...

=TRIM(RIGHT(SUBSTITUTE(TRIM(D5)," ",REPT(" ",LEN(D5))),LEN(D5)))

 

[Legacy 143009]

Oh God! This guy made my day Thaks a lot!!!

Hi,

try change "," with ";" in formula =TRIM(RIGHT(SUBSTITUTE(D5," ",REPT(" ",LEN(D5))),LEN(D5)))
maybe your regional settings...

 

[Ron Coderre]

Oh God! This guy made my day Thaks a lot!!!

Whew! Glad that got resolve.

Wouldn't NOW be the perfect time to add your LOCATION to your profile?
If we'd seen Sweden (or any of the other countries that use a semicolon instead of a comma) we might have spotted the issue more quickely.
While you're at it...Add the version of Excel you're using to your signature, too.