Text to Columns complex issue

[Braband4]

Hey All, first time posting - so in advance thank you for the help in the future and in the past when I search the forum.

Issue: I need to take a set of data and use text to columnsto separate out each step in a process. The steps are separated out but numbersexample (1., 2., 3. Etc), I haven’t found a way to do this using text tocolumns so I figured a formula would need to be involved.

Example A1:
1. Request 2. Initiate HAWA/SUNDRY 3. Plant Details 4. Add Material Info 5. MSDSand TDS 6. Approve to Stock 7. MRP Data 8. DMO Review - DG DMO 9. Post

Need it to separate out to columns by step so.
B1 would need to = 1. Request
C1 would need to = 2. Intiaite HAWA/Sundry
D1 would need to = 3. Plant Details

And so on.

 

[Ron Coderre]

Trying to use Text-to-Columns on that kind of data is like trying to use a garden rake to saw wood.
You'd do better to pursue:
- A Power Query approach
- Multiple SUBSTITUTE() functions followed by Text-to-Columns
- Multiple Find/Replace followed by Text-to-Columns
- VBA

 

[AlKey]

Try this
Enter formula in cell B1 and drag formula across

=TRIM(SUBSTITUTE(COLUMNS($A:A)&". "&TRIM(MID(SUBSTITUTE($A1,".",REPT(" ",200)),200*COLUMNS($A:A),200)),COLUMNS($A:A)+1,""))

 

[jtakw]

Hi,

Assuming data in A1, this formula (less prone to errors in case of other numbers within original text string), copied across as far as needed:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	1. Requested 2 times 2. Initiate HAWA/SUNDRY 3. Plant Details 4. Add 5 Material Info 5. MSDSand TDS 6. Approve to Stock 7. MRP Data 8. DMO Review - DG DMO 9. Post	1. Requested 2 times	2. Initiate HAWA/SUNDRY	3. Plant Details	4. Add 5 Material Info	5. MSDSand TDS	6. Approve to Stock	7. MRP Data	8. DMO Review - DG DMO	9. Post
Sheet375

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(MID($A1,FIND(COLUMNS($B:B)&". ",$A1),FIND(COLUMNS($B:B)+1&". ",$A1&COLUMNS($B:B)+1&". ")-FIND(COLUMNS($B:B)&". ",$A1))),"")

Formula produces Blank results when All Numbered Points are exhausted.