• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 35
  • Last Modified:

How do I parse text from one cell to another cell?

I would like to move row by row the space following the number through the closing parenthesis for each row to column B.  Since each line of data is not the same length, how do you write a macro to accomplish the task?  I would like to invoke the Do Until/While function if possible.

            A                                                                  B
25594831  Test.xlsm                                                                  (Too Tall)      
29354138 (Andrew Dillenger)      
22970876 (Olaniyi O Joshua-olagunju)      
27761698 (Charlie M Channington)      
29126146 (Nathan L Budafucco)
0
cowboywm
Asked:
cowboywm
  • 3
  • 2
  • 2
  • +2
1 Solution
 
FlysterCommented:
If you would like to do it without VBA you can use this formula. Paste it in B1 and copy down:

=MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1)

Flyster
0
 
Katie PierceCommented:
You can also utilize the Tsxt to Columns button on the Data ribbon, using "space" as the separator.
0
 
Rob HensonFinance AnalystCommented:
Katie, using Space as the separator would split the names across 3 columns in some cases; it would be better to use the ( as a separator. You can then use Find and Replace to get rid of the ) from the end of the result.

Thanks
Rob H
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
Try this.
Sub SplitNameAndNumber()
Dim arrNums As Variant
Dim arrNames As Variant

    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        arrNums = Evaluate("INDEX(LEFT(" & .Address & ",SEARCH("" ""," & .Address & ")-1),,1)")
        arrNames = Evaluate("INDEX(SUBSTITUTE(MID(" & .Address & ",SEARCH("" ""," & .Address & ")+2, LEN(" & .Address & ")), "")"",""""),,1)")
        .Value = arrNums
        .Offset(, 1).Value = arrNames
    End With
End Sub

Open in new window

0
 
Katie PierceCommented:
Good call, Rob.
0
 
cowboywmAuthor Commented:
This solution works the best and it's really fast, too.  Thank you so much.
0
 
Katie PierceCommented:
I think you chose the wrong solution as the accepted solution--it should have been Rob, correct?
0
 
cowboywmAuthor Commented:
No, the best solution was Norie's.  Don't know how to fix that.
0
 
Rob HensonFinance AnalystCommented:
Request Attention (at top of page) and ask for question to be re-opened for re-allocation of points.
0
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now