# Split string lowercase/uppercase challange

#### Flashbond

##### Well-known Member
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

##### MrExcel MVP
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

##### Board Regular
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

##### Well-known Member
=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

#### Flashbond

##### Well-known Member
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

##### Well-known Member
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

#### Flashbond

##### Well-known Member
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

##### Well-known Member
I can't see the value in D5 but there's nothing that I can see wrong with the formula.

Dom

#### Flashbond

##### Well-known Member
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

Last edited:

#### Flashbond

##### Well-known Member

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?

1,081,620
Messages
5,360,072
Members
400,567
Latest member
SarahR2908

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...