Separating values that are in a single cell

[kurtwagner]

Hello,
I need a macro that can separate values that are in a cell, separated by a space. For example, in a single cell I would have: 001234 12345 4456785 4545345

Suppose I have many of those cells in column A. I need a macro to split those in other lines below the original cell (inserting lines).

Now, a difficult part: Of the total amount of values in the original cell I only want to put in the lines below it the second, fourth, sixth (and so on) values.

Is it possible?

[CodeNinja]

Try something like this...

[VBA]Sub test()


Dim str() As String
Dim lrow As Long
Dim iArrayCount As Integer
lrow = 1
While Sheet1.Cells(lrow, 1) <> ""
str = Split(Sheet1.Cells(lrow, 1), " ")
For iArrayCount = 0 To UBound(str)
If iArrayCount > 0 Then
Sheet1.Rows(lrow + iArrayCount).Insert
End If
Sheet1.Cells(lrow + iArrayCount, 1) = str(iArrayCount)
Next iArrayCount
lrow = lrow + UBound(str) + 1
Wend

End Sub[/VBA]

[snb]

Where do you get these inconsistent data from ? (NB. 'Structuring precedes coding')

[kurtwagner]

The numbers in the cell means a Shipping Number followed by its invoice, and there are many shipping numbers and their invoices in a single cell.

CodeNinja, your macro works well, thanks, but gets all the values, and I need only the ones that would be the invoice (the second, the fourth, the sixth and so on). Is it possible to achieve that? Even if it is by deleting the undesired lines.

[snb]

and there are many shipping numbers and their invoices in a single cell.

If they originate from a txt/csv file we'd better use those files than an Excel file.

[kurtwagner]

They don't, they come from a single cell from SAP

[GTO]

...and I need only the ones that would be the invoice (the second, the fourth, the sixth and so on). Is it possible to achieve that? Even if it is by deleting the undesired lines.

Hi there,

Does that mean the value length is inconsistent? i.e., there may be six numbers in one record, four in another, and seven in another?

Mark

[kurtwagner]

Hi there,

Does that mean the value length is inconsistent? i.e., there may be six numbers in one record, four in another, and seven in another?

Mark

Unfortunately, yes...

[snb]

You had better posted a sample workbook !

[vba]
sub M_snb()
sn=sheets(1).cells(1).currentregion

for j=1 to ubound(sn)
if sn(j,1)<>"" then
sq=split(sn(j,1))
for jj= 0 to ubound(sq)
if jj mod 2=0 then sq(jj)=""
next
c01=c01 & " " & join(sq)
end if
next

sn=split(trim(c01))
cells(1,6).resize(ubound(sn)+1)=application.transpose(sn)
End Sub
[/vba]

[GTO]

As snb has suggested, an example workbook would likely result in better help. In .xls format would be preferable, and whilst the data can be fake, it should accurately correlate to what you are facing. That is - if there are occasional extra spaces, non-numeric values (123F123) that should be ignored, etc..., included, include these type oddities in the example.

Mark

[Teeroy]

If I understand the problem correctly the following should work. It is developed from SNBs earlier code (sorry SNB had to replace your trademark variables with something I could remember as I went).

[vba]Sub Split_Invoice_Number()
Dim aOutput()
vInput = Sheets(1).Cells(1).CurrentRegion
If IsEmpty(vInput) Then Exit Sub
For j = UBound(vInput) To 1 Step -1
If vInput(j, 1) <> "" Then
vTemp = Split(vInput(j, 1))
ReDim aOutput(1 To (UBound(vTemp) / 2))
For jj = 0 To UBound(vTemp)
If jj Mod 2 > 0 Then aOutput((jj + 1) / 2) = vTemp(jj)
Next
End If
Rows(j + 1 & ":" & j + (UBound(aOutput))).Insert
Cells(j + 1, 1).Resize(UBound(aOutput)) = Application.Transpose(aOutput)
Next
End Sub
[/vba]