Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Parse everything after the comma

I have values in a column that I need to parse out the values that are after each comma in the string.  Leaving the original column intact.
1234-1234,AO-16,AO-19,HJ-39,TH-20
1234-1234,AO-16,AO-19,HJ-39
1234-1234,AO-16,AO-19
1234-1234,AO-16

In the result location, which I would like in the column just to its right have the hyphen replaced with an underscore.
It does not matter if it is in a formula, or vba, but perfer a vba solution. Thanks.

Resulting columns would look something like:
1234-1234,AO-16,AO-19,HJ-39,TH-20     AO_16     AO_19     HJ_39     TH_20
1234-1234,AO-16,AO-19,HJ-39               AO_16     AO_19     HJ_39
1234-1234,AO-16,AO-19                        AO_16     AO_19
1234-1234,AO-16                                   AO_16
0
RWayneH
Asked:
RWayneH
  • 3
  • 3
2 Solutions
 
Dan CraciunIT ConsultantCommented:
See the attached file, using Patrick Matthews's RegExpFind function.

HTH,
Dan
test.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
Why not simply make a copy of the first column in the second column and then perform a text-to-column on the new column?
0
 
Saqib Husain, SyedEngineerCommented:
Here is a macro to do this

Sub Macro1()
    Range("A:A").Copy Range("B:B")
    Range("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
End Sub
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Dan CraciunIT ConsultantCommented:
@Saqib Husain, Syed: because you need a SUBSTITUTE after the TTC: you need to change "-" to "_"
0
 
Saqib Husain, SyedEngineerCommented:
Sorry, I missed that part but that still does not justify the use of regex

Here is an updated macro.

Sub Macro1()
    Range("A:A").Copy Range("B:B")
    Range("B:B").Replace what:="-", replacement:="_"
    Range("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
End Sub
0
 
Dan CraciunIT ConsultantCommented:
No, it does not. But I like regular expressions and still think it's cleaner and more readable.

=SUBSTITUTE(RegExpFind($A1,"\w*-\w*",2),"-", "_")
0
 
RWayneHAuthor Commented:
Thanks for the help.
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

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.

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