Breaking up contents of a cell

[mellen]

I've got a cells lined in a column that have too much information in it. I'm trying to break this information up into multiple columns, extracting certain bits of information from the original cell. ie. a cell has information like: size, colour, shape etc. There are multiple attributes and different permutations of each. I want each of those attributes to be its own column.

There are predefined sizes, colours etc. that I can search against, but can't figure out how to.

Thanks in advance

 

[erik.van.geit]

Hello, mellen
Welcome to the Board !!!!!
seems like you could use
Text To columns (menu data)
posting some sampledata would help

kind regards,
Erik

 

[mellen]

Hi Erik,

I looked at the text to column, but there are whitespaces that separate some of the information. Here are some examples

5 Z 1/2 BRN; so what I need in (3) separate columns here are:
5, Z 1/2, BRN. Another example:
2 MBCL DB; here I need 2, MBCL and DB separated under the same 3 columns.

Thanks again in advance

 

[SteveO59L]

As Erik has said, use Text to Columns, with a space as the delimiter. You can always then concatenate columns if this does not produce entirely the desired result.

 

[mellen]

Am I able to automate such a process? There are > 30000 entries also. If I can automate this, then that's sweet.

 

[erik.van.geit]

Yes you can automate this.
What are the rules to follow ?
would it be
if 3 columns do nothing
if 4 columns put 2 and 3 together

or do you have some other possibilities to check ,

best regards,
Erik

 

[erik.van.geit]

with rules mentioned above

Option Explicit

Sub reorganize_columns()
'Erik Van Geit
'051101 1200
'EXAMPLE
'BEFORE                     AFTER
'a1    b1    c1    d1       a1    b1c1  d1    changed
'a2    b2    c2             a2    b2    c2
'a3    b3    c3    d3       a3    b3c3  d3    changed

Dim LR As Long
Dim LC As Integer
Dim rng As Range
Dim arr As Variant
Dim I As Long
Dim J As Integer

Const FR As Long = 1    'first row with data
Const FC As Integer = 1 'first column
Const NR As Integer = 4 'number of columns where data can occur

LR = Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
LC = FC + NR - 1
Set rng = Range(Cells(FR, FC), Cells(LR, LC))

arr = rng
    For I = FR To LR
        If arr(I, 4) <> "" Then
        arr(I, 2) = arr(I, 2) & arr(I, 3)
        arr(I, 3) = arr(I, 4)
        arr(I, 4) = "changed"
        End If
    Next I
rng = arr
End Sub

 

[mellen]

Erik,

Thanks, I'll play around with the code, the logic unfortuately isn't as straight forward as I would have liked, but that's life.

Just wondering though, as this file is generated nightly (from a database export), I need to run all the steps separately and so was hoping to be able to automate the text to column process into a new sheet also.

Thanks again in advance.

Len

 

[Peter_SSs]

mellen

Welcome to the Mr Excel board!

I don't suppose that we would be lucky enough for the first and last sections of each cell to have no spaces - like your two examples? If so, then this might help.

B1: =LEFT(A1,FIND(" ",A1)-1)
C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,""))
D1: =RIGHT(SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

All formulas copied down.

Mr Excel.xls
	A	B	C	D
1	5 Z 1/2 BRN	5	Z 1/2	BRN
2	2 MBCL DB	2	MBCL	DB
3	7 KPL 3/4 BHYUOPK	7	KPL 3/4	BHYUOPK
4	8 A (-) 3/4 YLW	8	A (-) 3/4	YLW
Break Up Cell

 

[erik.van.geit]

was hoping to be able to automate the text to column process into a new sheet also.

you can record a macro doing that
like others here, I would say "the macro recorder is our best friend in VBA-development"
try out and post back if any problems