Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Parse out characters in an Excel cell

Posted on 2011-10-12
3
Medium Priority
?
201 Views
Last Modified: 2012-08-14
Hi there,
Wondering if anyone can help me out here...I have a file where in one cell, it has the Full Name and their email address. What I would like is to just capture on the adjacent cell, the name before the @ symbol within the email address. Would it be possible? Logon Names
So the end look would be this:
B                                                      C
John Smith (jsmith@xxx.com)         jsmith


Thanks for your help, and please let me know if you need additional info/details...

Much appreciated,
Classic
0
Comment
Question by:Classic1
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 36956103
With data in B2 try this formula in C2 to extract the information you need

=MID(LEFT(B2,FIND("@",B2)-1),FIND("(",B2)+1,99)

regards, barry
0
 

Author Closing Comment

by:Classic1
ID: 36956376
Awesome!!!
Wow...thanks for the quick replay, barryhoudini, that worked exactly to a tee!!!

Just breaking down the syntax, you're using a MID function and using the FIND function to find the "@" symbol and start going LEFT 1 spot  previous (-1). Then stop just before the "(" symbol (+1). The 99 is just in case there's more than 99 characters in the string?


Much appreciated!!!
Classic
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36956443
Yes, it's a little like that but not quite....

This part

=FIND("@",B2)

finds the position of the @ and then when you use that in LEFT function like this

=LEFT(B2,FIND("@",B2)-1)

that gives you everything to the LEFT of the @, e.g. for your example

John Smith (jsmith

Now you use that within the MID function taking  everything from the position of the ( for 99 characters (or the end of that substring).

If you use REPLACE instead of MID then the limit of 99 is immaterial, i.e.

=REPLACE(LEFT(B2,FIND("@",B2)-1),1,FIND("(",B2),"")

regards, barry
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question