Parsingv1

EE Pros,

I have 100+ records WS that look exactly like the one attached (except I've only included a single record that has the consistent format for all records/cells.  They are all in a single column by cell.  I need a macro that can parse the data by Identifying the Name (e.g. John Doe, strip the dash out and spaces, place the title (e.g. Global Director, Business Management) in next column, identify the email and place it in next columnI and place the phone number in next column.  In other words, when finished, I have the record with the data in separate cells.  

For someone who knows how to manipulate data, I think this may be rather easy.  For someone like myself, not so much!

Thank you in advance,

B.
Parsing-Data-in-Cell.xlsm
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Does the data always start at row 7 and column F?
0
Rgonzo1971Commented:
Hi,

pls try

Sub macro1()
For Each cell In Selection
    Pos = InStrRev(cell, " ")
    Res = Left(cell, Pos - 1) & "|" & Mid(cell, Pos + 1)
    Pos = InStrRev(Res, " ")
    Res = Left(Res, Pos - 1) & "|" & Mid(Res, Pos + 1)
    Res = Replace(Res, " - ", "|")
    aRes = Split(Res, "|")
    cell.Offset(0, 1).Resize(1, UBound(aRes) + 1).Value = aRes
Next
End Sub

Open in new window

Regards
0
Bright01Author Commented:
No.   I can actually have it start in any column.  It's not column dependent.  If you tell me where to start the first record, I can work with that.

Thank you!

B.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Martin LissOlder than dirtCommented:
Change the two Const values as needed.
Sub Parse()

Dim lngLastRow As Long
Dim lngRow As Long
Dim intPosStart As Integer
Dim intPosEnd As Integer
Const START_COL = "F"
Const START_ROW = 7

lngLastRow = Range("F1048576").End(xlUp).Row

For lngRow = START_ROW To lngLastRow
    ' Find the name
    intPosStart = InStr(1, Cells(lngRow, START_COL), " -")
    Cells(lngRow, "G") = Mid$(Cells(lngRow, START_COL), 1, intPosStart)
    
    ' Find the title
    intPosEnd = InStr(intPosStart, Cells(lngRow, START_COL), "@")
    intPosEnd = InStrRev(Cells(lngRow, START_COL), " ", intPosEnd)
    intPosStart = InStrRev(Cells(lngRow, START_COL), "- ", intPosEnd)
    Cells(lngRow, "H") = Mid$(Cells(lngRow, START_COL), intPosStart + 2, intPosEnd - intPosStart - 2)
    
    ' Find eMail
    intPosStart = InStrRev(Cells(lngRow, START_COL), " ", intPosEnd)
    intPosEnd = InStr(intPosStart + 1, Cells(lngRow, START_COL), " ")
    Cells(lngRow, "I") = Mid$(Cells(lngRow, START_COL), intPosStart + 1, intPosEnd - intPosStart - 1)

    ' Phone
    Cells(lngRow, "J") = Mid$(Cells(lngRow, START_COL), intPosEnd + 1)
    
Next

Open in new window

0
Martin LissOlder than dirtCommented:
Here's a documented version.
Sub Parse()

Dim lngLastRow As Long
Dim lngRow As Long
Dim intPosStart As Integer
Dim intPosEnd As Integer
Const START_COL = "F"
Const START_ROW = 7

lngLastRow = Range("F1048576").End(xlUp).Row

For lngRow = START_ROW To lngLastRow
    ' Find the name...
    ' by locating the " -"
    intPosStart = InStr(1, Cells(lngRow, START_COL), " -")
    ' The name is everything from 1 to the start of the " -"
    Cells(lngRow, "G") = Mid$(Cells(lngRow, START_COL), 1, intPosStart)
    
    ' Find the title...
    ' by starting at the previous start position and finding the "@" that follows it
    intPosEnd = InStr(intPosStart, Cells(lngRow, START_COL), "@")
    ' then looking backward for the first space which will be the end of the title
    intPosEnd = InStrRev(Cells(lngRow, START_COL), " ", intPosEnd)
    ' then looking backward from there for the "- " which will be the start of the title.
    intPosStart = InStrRev(Cells(lngRow, START_COL), "- ", intPosEnd)
    ' The title is everything in-between
    Cells(lngRow, "H") = Mid$(Cells(lngRow, START_COL), intPosStart + 2, intPosEnd - intPosStart - 2)
    
    ' Find eMail...
    ' by looking backward from the position of the "@" for the first space which will be the start of the email
    intPosStart = InStrRev(Cells(lngRow, START_COL), " ", intPosEnd)
    ' and sthen looking forward to the next space which will be the end of the email.
    intPosEnd = InStr(intPosStart + 1, Cells(lngRow, START_COL), " ")
    ' The nmail is everything in-between
    Cells(lngRow, "I") = Mid$(Cells(lngRow, START_COL), intPosStart + 1, intPosEnd - intPosStart - 1)
    
    ' Phone
    ' The phone number is everything after the end of the email
    Cells(lngRow, "J") = Mid$(Cells(lngRow, START_COL), intPosEnd + 1)
    
Next

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Thanks guys!  Both solutions work for me.  I'm going to leverage Martin's for my parsing.  But thanks to both of you for being so responsive!


B.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.