Split column macro that contains Underscore

Question

I know this is doable, but I didn't see the code in the database.  I am looking for a macro that I can insert into another large macro wherein I can split a column that is formatted as so:  KKKK_KSDKL  I would like to split it at the underscore.  I'll be using only the last part of the text and the text before the underscore will be deleted.  Does anyone have the code already written?

Thanks much!

Answer 1

In the following code, change Range("C2:C100") to the range that you want to split. You can use a variable of type Range, of course.

    With Range("C2:C100")

        .TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _

            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="_", _

            FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True

    End With

Answer 2

Using Text-to-columns will do it, but may cause problems if you have other columns of data.

Stepping through the cells of column A, for example:

For Each myC In Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp))

myC.Value = Split(myC.Value, "_")(1)

Next myC

Bernie

Answer 3

That is because the original poster stated "I'll be using only the last part of the text and the text before the underscore will be deleted.". So the code skips the first element. In the argument

FieldInfo:=Array(Array(1, 9), Array(2, 1))

Array(1,9) specified how to treat the first column; 9 means "skip it".

If you omit the FieldInfo argument, the first element will be included:

With Range("A1:A100")

.TextToColumns Destination:=.Range("B1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=True, Comma:=False, Space:=False, Other:=True, OtherChar:="!", _

TrailingMinusNumbers:=True

End With

Answer 4

Hi,

this is a very usefull subrutine, but, i don't understand why it doesn't take, the first value of column.

Example:

One;two;three;four;five

i See:

Two|three|four|five

With Range("A1:A100")

.TextToColumns Destination:=.Range("B1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=True, Comma:=False, Space:=False, Other:=True, OtherChar:="!", _

FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True

End With

Answer 5

Here is a UDF - can be adapted to be part of subrountine. best wishes

Function mysplit(mycell)

 hyphen = WorksheetFunction.Find(Chr(95), mycell)

 mysplit = Mid(mycell, hyphen + 1)

End Function

Edited to change CHR(45) to CHR(95) ---silly me