Looking for Excel code to split cell out by commas 3

[smooveb]

Hi Everyone,

I've got the following text in one cell:

BA DD,Unix Admin[38%],DBA[40%],Network Data[8%],ITQM[26%],Integrator,Wintel Admin[38%],BA GTS[38%],EUT Tech[38%]

Using Excel VBA, what I want to do is split this out into different rows, such as this:

BA DD
Unix Admin[38%]
DBA[40%]
Network Data[8%]
etc...

Once I get this far, I'll tackle the best way to get the numbers separated into two adjacent cells (i.e. Unix Admin in column A with 38 a numeric value in column B)

Thanks for any and all ideas!

Barrett

 

[AdrianScaife]

Excel XP has a useful function on strings:

Function Split(Expression As String, [Delimiter], [Limit As Long = -1], [Compare As VbCompareMethod = vbBinaryCompare])

you can use in a varity of ways depending what you need.

Otherwise without using VBA try Data-Text to Columns

 

[Deleco]

Try this!

I think this does what you need, if i understood you correctly

Private Sub SplitAddtoRows()
Dim sText As String
Dim arrValues() As String
Dim iCount As Integer

    sText = Worksheets(1).Cells(1, 1).Value
    arrValues = Split(sText, ",")
    
    For iCount = 0 To UBound(arrValues)
        Worksheets(1).Cells(iCount + 4, 1).Value = arrValues(iCount)
    Next iCount
    
End Sub

Deleco

 

[VBAjedi]

The Split function is handy, but not available in XL97. If you or any of your users still use 97, you'll have to take an approach that's a bit longer:

Dim ArrValues(100) As String
A = Range("A13").Value
y = 1
For x = 1 To Len(A)
   B = Mid(A, x, 1)
   If B = "," Then
      y = y + 1
   Else
      ArrValues(y) = ArrValues(y) & B
   End If
Next x

Once ArrValues is filled you can use it as Deleco suggested.

Let us know if that does what you're after!

VBAjedi

 

[Loomah]

Hi
This is just the same thing without the looping, assuming data is in cell A1 and lists it below

Sub a()
Dim vArr As Variant
With Range("A1")
    vArr = Split(.Text, ",")
    Range(.Offset(1, 0), .Offset(UBound(vArr) + 1, 0)) _
        = Application.Transpose(vArr)
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[VBAjedi]

Slick, Loomah - hopefully Barrett and his users don't use XL97 so they can use your snippet. Remember - XL97 doesn't have Split or Join.

VBAjedi

 

[Loomah]

Ooooo Sooooooo trooooo!!
I sort of bypassed xl97, moving from 95 to 2k and it looks like I might make an even bigger jump when I get a new PC, missing out XP altogether!

If there are likely to be users of different versions test for thst first

eg
If Application.Version >= 9 Then MsgBox "xl2k+"

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[smooveb]

you guys ALL ROCK