PDA

View Full Version : Separating values that are in a single cell



kurtwagner
12-03-2012, 12:22 PM
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
12-03-2012, 01:08 PM
Try something like this...

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

snb
12-03-2012, 01:41 PM
Where do you get these inconsistent data from ? (NB. 'Structuring precedes coding')

kurtwagner
12-03-2012, 02:44 PM
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
12-03-2012, 04:11 PM
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
12-03-2012, 04:21 PM
They don't, they come from a single cell from SAP

GTO
12-03-2012, 06:06 PM
...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
12-04-2012, 01:46 AM
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
12-04-2012, 03:31 AM
You had better posted a sample workbook !


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

GTO
12-04-2012, 05:07 AM
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
12-04-2012, 11:10 PM
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).

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