Simple Parcing of Data

Bright01
Bright01 used Ask the Experts™
on
EE Pros,

I have Email addresses that are NAME/Location/Location/Location formatted in a single cell.

I need to have a macro or formula that extracts and leaves only THE NAME.   So, in other words, when it reads the cell, and sees the first "/", it will delete the "/" and everything after that in the same cell.

Thank you in advance.

B.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
=LEFT(A2,FIND("/",A2)-1)

EDIT: Oh, that won't do it in the same cell.
Rob HensonFinance Analyst
Commented:
Use Text to Column Wizard with / as separator.

In last step, select "Do Not import" for the columns with the data that you don't want.
Microsoft Excel Expert
Top Expert 2014
Commented:
please try

assuming your data is in cell A1 then put this in B1  drag down

=TRIM(MID(SUBSTITUTE($A1,"/",REPT(" ",LEN($A1))),1*LEN($A1)-(LEN($A1)-1),LEN($A1)))
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Rob HensonFinance Analyst

Commented:
As Missus points out in her first comment, a formula will not do it in the same cell.  To do it in the cell will have to be Text to Column as I suggested or with a macro.

Author

Commented:
Great job!  Fast and both worked.   Thank you for the help.
Rob HensonFinance Analyst

Commented:
If you're going to accept JimJam's formula, you ought to have given points to Missus as well.

Any reason why you accepted formula as best when you specified you wanted in same cell?

Author

Commented:
Jimjam's formula allowed me to copy and paste it down a complete column and it worked.  I also had 3 "/" in the same cell and everything right of the first "/" was deleted leaving me with exactly what I needed.

B.
Rob HensonFinance Analyst

Commented:
And so would LEFT function

Author

Commented:
Rob,

Just tested her code and you were right!  It worked also.  I owe Miss Sellaneus  some points and an apology........  I'm afraid I dismissed her answer when she mentioned it didn't work within the same cell.

Thanks for calling this out.

B.
Rob HensonFinance Analyst

Commented:
That is what the LEFT function is designed to do -  show everything to the left of a given point, in Missus formula that given point was the first occurrence of "/".
leaving me with exactly what I needed.
this might be the case but it isn't what you asked for.

Missus in some ways rightly pulled her answer because you asked for
everything after that in the same cell
.

Author

Commented:
Well, as in life, sometimes we adapt to the situation.  I could have actually gone either way.... in the same cell or as a result of the cell containing the original data.  My fault for not looking harder at her answer.   I'll need to be more careful on these responses.

Again, thanks for pointing out the fact that her code worked with only a minor change to the original "ask".

B.
Rob HensonFinance Analyst

Commented:
No worries, just ensuring an answer which is as full as possible for people searching for answers in future.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial