[Solved] Split column; some numbers go, some remain

[Islander206]

Hello everybody, this is my first question on the forum.
I have data copied from PDF, it goes like this, all in a single column:

Music Box 1 0 4 10
Carillon 9 4 14
Pop Vibe. 1 4 11
Tubular-bell 0 4 14
Vibraphone w 8 4 11
Music Box 2 1 4 10

I want to split this column so that the last three numbers of each cell go to separate columns:

___A____________B____C___D
Music Box 1_______0___4___10
Carillon__________9___4___14
Pop Vibe.________1___4___11
Tubular-bell______0___4___14
Vibraphone w_____8___4___11
Music Box 2_______1___4___10

As you can see, some numbers must remain in the first column because they belong to the instrument names.
Any help would be greatly appreciated.

[Zizi64]

I think, there is no any predefined "one step" way to determine which number must remain in the first column. Maybe you can determine that with a custom macro routine.

[acknak]

Here's how I handle such tasks:

1) Select the cells you want to change
2) Edit > Find & Replace
Search for: \s+(\d+)\s+(\d+)\s+(\d+)\s*$
Replace with: \t$1\t$2\t$3
Options/Current selection only: ON
Options/Regular expressions: ON

Click Replace All

Now, select the cells (or column) again, and

Data > Text to Columns ...
Separated by: TAB

Click OK

[Lupp]

It can be done by an engine of formulae (See attached!) using some helper columns, or by a sequence of 'Find & Replace' and two subsequent applications of the 'Text to Columns...' tool.

[Islander206]

acknac and Lupp, thank you both very much for your time and willing to help.

acknac's Find and Replace formula worked excellent, so I'm going to use it for the rest of my data (there is much more of it than in my post).
Lupp, unfortunately, your F&R formula returned some strange results. For example, original data:

Pad With 81 4 89
LA Warm Pad 82 4 89
Attack! Pad 83 4 89

after Replace All became like this:

Pad With 8#1 4 89
LA Warm Pad 8#2 4 89
Attack! Pad 8#3 4 89

"#" symbol ended up on wrong spot...

Anyway, thanks again, for helping me out!

[Lupp]

... unfortunately, your F&R formula returned some strange results. For example, original data:
Pad With 81 4 89 ...
... after Replace All became like this:
Pad With 8#1 4 89

Yes, of course. This was the first step and you didn't do the other two. The # was used to set an unambiguous splitting point for the second step. @acknak applied the same idea in a different way using the "tab character" as intermediary delimiter. I think his solution is, as far as interactive means are concerned, clearly better. In specific it doesn't need a third step. I wouldn't have published my version of this "replace and columnise" procedure if my post not crossed his.


Please note: My suggestion is primarily to apply the solution by formulae. You won't need to repeat two or three somewaht complicated steps after appending some new data, e.g. Just feed them into the engine ...

[Islander206]

Dear Lupp, now I see that your advise is more productive in the long run. Sorry for not trying to perform you method all the way to the end, I definitely will give it a try.

===============================

It works like a charm, thank you. Please don't get me wrong, but let me explain: if I needed to do this conversion on a regular basis, new data every day, I would definitely use your formulae to speed up the process. But all I need to do is take a PDF user manual and turn it into more convenient spreadsheet form, and I need to do it just once.

So, my problem solved, thank you guys for your time and patience.