Need help parsing data

[nmoore2300]

Hello, I cannot figure out how to parse the data in the attached sheet.

Column A is an ID that is tied to several sub id's listed in column B. I want to parse out each of the sub id's into a seperate column. Could have the entire entry (number + text) or just simply the account number.
Note column B sometimes contains one account, or two... or even three. And in one case the account doesn't start with a "6" but and "8".

I would prefer to do this without VBA if possible. Either using the text to columns functionality or the functions for "right", "left", "mid". But I just haven't cracked it ...yet.

Thanks for any advice!

~N

[FDibbins]

Not entirely sure what your expected outcome needs to look like - could you add a few sample answers?

[TMS]

Use Text to Columns to split column B using Char(10) (New Line) to columns C:E. I've left column F blank

Then, in G1:

Please Login or Register  to view this content.

which will spill across.

And, in cell G3:

Please Login or Register  to view this content.

which will spill down ... copy across.

Adjust column widths as required.

Copy and Paste Special Values if you want to get rid of the formulae.

[nmoore2300]

Wow. This seems to have worked perfectly.

Can you explain how you use Char10 in the text to columns? That's my solution right there that I couldn't figure out how to do.

Thank you!
~N

[nmoore2300]

CTRL+J !!!!

OMG that's amazing. haha.

Thank you TMS. Brilliant as you Brits say.

[TMS]

You're welcome.


Maybe over engineered if you just needed to split the data. But it was an interesting challenge .



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.