Dynamic Text To Column

[Warryk]

I'm trying to create a dynamic text to column but i have no idea how to do this. Basically I have a cell that will contain quantity and sizes of apparel and may look something like this:
20/s 13/M 14/L

I can do a text to column that will pull this information out into individual cells to look like this:

20

S

13

M

14

L

<tbody>
</tbody>

That part was easy, and having the data like that allows me to do a lot of other things with it that I need to. The problem is, the original cell's values may change and I don't want to have to redo the text to column feature every time that happens. I'll be inputting size data like this on 20 or more lines as well, so I'd much rather have something that updates automatically.

Any suggestions?

 

[Rick Rothstein]

Assuming the column you will be putting the data in is Column A, give this event code procedure a try...

Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range, Cell As Range, Txt As String Set Rng = Intersect(Target, Columns("A")) If Not Rng Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each Cell In Rng Txt = Cell.Value Cell.Offset(, 1).Resize(, Columns.Count - 1).ClearContents Cell.Offset(, 1) = Application.Trim(Replace(Txt, "/", " ")) Cell.Offset(, 1).TextToColumns Cell.Offset(, 1), xlDelimited, , , 0, 0, 0, True Next Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Snakehips]

Warryk,

In the light of Rick's above code you may not wish to use a formula but here is a possibility of such....

Excel 2007
	A	B	C	D	E	F	G
2	20/s 13/M 14/L	20	s	13	M	14	L
3	201232499/sssssssvs 1399999v/Mmmmmmmmv 149999999v/Lllllllllllllv	201232499	sssssssvs	1399999v	Mmmmmmmmv	149999999v	Lllllllllllllv
Sheet2

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,"/", " ")," ",REPT(" ",99)),(COLUMNS($B1:B1)-1)*99+1,99))

 

[Warryk]

Warryk,

In the light of Rick's above code you may not wish to use a formula but here is a possibility of such....
Excel 2007

	A	B	C	D	E	F	G
2	20/s 13/M 14/L	20	s	13	M	14	L
3	201232499/sssssssvs 1399999v/Mmmmmmmmv 149999999v/Lllllllllllllv	201232499	sssssssvs	1399999v	Mmmmmmmmv	149999999v	Lllllllllllllv

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B2 =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,"/", " ")," ",REPT(" ",99)),(COLUMNS($B1:B1)-1)*99+1,99)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This is exactly what i was looking for. Thanks!