Split string lowercase/uppercase challange

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
668
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
Joined
Jan 14, 2009
Messages
2,300
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
Joined
Jun 17, 2011
Messages
124
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
Joined
Jan 18, 2005
Messages
7,292
=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
Joined
Mar 13, 2010
Messages
668
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
Joined
Jan 18, 2005
Messages
7,292
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
Joined
Mar 13, 2010
Messages
668
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
Joined
Jan 18, 2005
Messages
7,292
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
Joined
Mar 13, 2010
Messages
668
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
Joined
Mar 13, 2010
Messages
668

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?
 

Forum statistics

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

Some videos you may like

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...
Top