TEXT TO COLUMN WITH VERTICAL RESULTS

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
167
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
167
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,826
Messages
5,766,669
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top