TEXT TO COLUMN WITH VERTICAL RESULTS

[JuicyMusic]

Hi, I hope someone can help.

I have a very long string of inventory part names in a cell. I don't know why our Inventory department enters all the parts that were used on one job in one cell instead of listing it in seperate rows.

I would like for the parts within this string to be listed vertically please, with a Macro.

1) Each part name within the string has a number, a ")", and a space before it.

Example: 2) Jack hammer, 5) Sprickets(silver) 2) Brooms 5736, 7) Ryobi Drill #6745(new), 4) Batteries (charged)

2) Some part names and part numbers may contain a "(" and/or a ")" anywhere within their name.

3) The number before the ")" stands for the quantity of that part that was used.

Example: 5) Sprickets (silver) means five silver sprickets were taken out of inventory.

RESULTS:

2) Jack Hammer
5) Sprickets(silver)
2) Brooms 5736
7) Ryobi Drill #6745(new)
4) Batteries (charged)

Is it possible to account for a typo, such as someone entering a "(" after the quantity number instead of a ")"? I see this typo often.

Example: 4( Batteries (charged)

Thank you so much.

Juicy

 

[JuicyMusic]

Hello again,
I would like to provide this code i located. I am not able to download XL2BB on my Nook right now.
My data will be in Column F2. F1 is the header name.

PLEASE NOTE THAT MY DATA IN EACH COLUMN BELOW DOES'NT REALLY HAVE ANY BRACKETS BEFORE OR AFTER. I ADDED THEM HERE FOR VISUAL PURPOSES ONLY.

COLUMN COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F
(job #745) (6/5/20) (labor names) (type of work) (some #) (LONG STRING)
(job #'842) (6/7/20) (labor names) (type of work) (some #) (LONG STRING)
(job #'345) (6/8/20) (labor names) (type of work) (some #) (LONG STRING)
(job #844) (5/28/20) (labor names) (type of work) (some #) (LONG STRING)
(job #9100) (5/15/20) (labor names) (type of work) (some #) (LONG STRING)
(job #6237) (7/1/20) (labor names) (type of work) (some #) (LONG STRING)

HERE IS MY OBSTACLE: How do i get the result of the deliminated verticle string to appear on its own tab, with the tab name being the job date, or named whatever is in Column B? I need it to loop thru the data.

Also, how do i add the code to copy down the data in Column A thru Column E, to the same number of rows that the vertical deliminated string will have......on the new tabs?

Example: If the deliminated result takes up "x number of rows" then copy down data in Column A thru Column E the same number of times down.......or to the last row of data in Column F.........Next.

Thank you, thank you, thank you, in advance!

Sub texttocolumns()

'Declaring variables
Dim StartRow, i, LastCol As Long
Dim Rng As Range

'disabling the display alerts
Application.DisplayAlerts = False

'Initializing the variable
StartRow = 10
Set Rng = Range("A10")

'Separting the text based on the delimiter
Rng.texttocolumns Destination:=Rng.Offset(0, 1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Other:=True, OtherChar _
:="-"

'Finding the column number of cell containing the last string after delimiting
LastCol = Rng.End(xlToRight).Column

'arranging the text in columns to rows
For i = 2 To LastCol
Cells(10, i).Cut Cells(StartRow, 2)
StartRow = StartRow + 1
Next i


End Sub

 

[JuicyMusic]

Help anyone!