Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Parse out characters in an Excel cell

Posted on 2011-10-12
Medium Priority
201 Views
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?
So the end look would be this:
B                                                      C
John Smith (jsmith@xxx.com)         jsmith

Much appreciated,
Classic
0
Question by:Classic1
• 2

LVL 50

Accepted Solution

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

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

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

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.
###### Suggested Courses
Course of the Month21 days, 7 hours left to enroll