Splitting columns in Excel 2003 and XP

  exdragon 12:51 23 Nov 2009

Hi - I've got a spreadsheet which has columns of titles of photographs together with the number of points they have been awarded in a competition. The titles can vary from one word upwards, and they're shown as, for example, 'Early morning 9 points'

The spreadsheet from which this information is taken cannot be altered at all, as it is part of an integrated package used specifically for these types of competitions.

I'd like to be able to show the '9 points' in a separate column after the title (in my copy of the spreadsheet). Using Text to Columns won't work, as I don't know how many 'extra' columns to add to take into account the number of words in the titles.

Is there any way to move just the last two items (the '9' and the 'points') into a separate column?

  interzone55 13:16 23 Nov 2009

Hopefully Vog will see this, but in the meantime I'll try

are the points single digit, so the text to be moved is always 8 digits...

  exdragon 13:29 23 Nov 2009

No - can be from 6 to 15.

What I've also just realised is that, if an image is accepted for the competition, there can be either (A) or (HM) after the word points. Both letters are in brackets and need to stay with the number of points shown.

  VoG II 13:40 23 Nov 2009

This will do it I think

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198))

  exdragon 13:48 23 Nov 2009

Thanks, VoG™, but what do I do with it??

  VoG II 13:51 23 Nov 2009

Say your first record is in A1 then in a spare column in row 1 enter the formula and drag down as far as necessary.

If the data actually starts in M2 then in a spare column row 2 enter

=TRIM(RIGHT(SUBSTITUTE(M2," ",REPT(" ",99)),198))

and drag down.

  exdragon 14:01 23 Nov 2009

VoG™ - I've said it before and I'll say it again. will you marry me?


  exdragon 14:02 23 Nov 2009

But not yet - it's picked up the 'points (A)' but not the actual number of points.

Unless I've missed something

  VoG II 14:06 23 Nov 2009

If it is basically the last three 'words' then try


  exdragon 14:12 23 Nov 2009

Hmm, perhaps it isn't possible - here's a sample

1. After The Fire 9 Points
1. Dancing 9 Points
1. Misty Morning 9 Points
1. Nearly there Girl 12 Points (A)

So as you can see, it can be 2 words or 3 if there's an (A) included.

Might be bit too complicated, do you think?

  VoG II 14:17 23 Nov 2009

Starts to get awkward. Try


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Intel Coffee Lake release date and specifications

12 Amazing British Craft Beer Label Designs

watchOS 4 review

Les meilleurs navigateurs internet 2017