Clever way to split cells?

Hi CC

Try a find a replace function first.

Try finding space 2 to replace with ,2

Then on the text to columns, select delimited then comma and finish. 

Bit more fiddling and you should have what you need?

Kind Regards

John

If it is always

going to be a letter in Col 3 and a 4 digit year in Col 2 with one space between, you could do the following (assuming you are analysing A1):

Col 3 =Right(a1)

Col 2 =mid(a1,len(a1)-5,4)

Col1 =Left(a1,len(a1)-7)

This may however leave trailing spaces in column 1 if that is important

Another way

Not sure if your example was illustrative of your problem but you have you considered:

If for example you have 'Mrs B M Jones 2012 B' in A1

=RIGHT(A1,1) will pull out 'B'

=LEFT(RIGHT(A1,6),4) will pull out '2012'

and for good measure

=LEFT(A1,LEN(A1)-7) returns 'Mrs B M Jones'

Just a thought.

Regards
Mark

Whoops - took my eye off the ball and made a coffee whilst Paul provided the same answer!

Formula Time

If all the cells are formulated in the same way i.e. a letter and a 4 digit year at the end then asuming the details are in Column A, you could use the following formulas;

Column B: =LEFT(A1,LEN(A1-7))

This takes everything on the left hand side of the cell and removes the last 7 characters (4 digits, 1 letter, and 2 spaces).

Column C: = MID(A1,LEN(B1)+2,4)

Takes the mid section starting after the name plus the first space and inserts the 4 digits for the year.

Column D: = Right(A1,1)

Chooses the last character.

(Obviously A1, B1 will need replacing with the correct cell references from your sheet)

You can then copy and paste special the values in those 3 columns and delete the first column, if you want.

Hope this helps.

EDIT: I didn't make a coffee, but I did apparently keep the window open whilst doing other things long enough for several people to beat me to the punch.

Ok - it can still be done

This is not as long winded as it sounds and will speed things up if you have a lot of data:

Create a second column with a repeat of the first column data

Use text to columns to split out the second column into the constituent elements

Well to the right, enter a formula in, say, K1 =counta(b1:j1) where B1 to J1 is the where the text to columns data is. Move K and J to the right if there could be a lot more columns used.

In column N, the formula is =OFFSET($B1,,$K1-1)

In Column M, the formula is: =OFFSET($B1,,$K1-2)

In column L, the formula is =LEFT(A1,LEN(A1)-LEN(M1)-LEN(N1)-2)

Obviously you can move these around and adjust as appropriate.

You may wish to use the TRIM function on your initial data to remove any double spaces

Mix and Match approach

Constantly Confused,

Using your provided data, here's what I've done in Excel 2003. It'll only really work if there's only one column that has multiple elements:

Using Data | Text to columns with a space delimiter separate everything into separate columns:

MrASmith2012A MrsBMJones2012BMrsHildaGreen2013C 

So, there are a maximum of six columns. Up to 4 will comprise the first entry and the remaining two will be the other two entries

In column G (immediately to the right of the last populated column, column F) I put a formula which counts the number of entries. In G1 is the following formula =COUNTA($A1:$F1). I copy this down to give:

MrASmith2012A 5MrsBMJones2012B6MrsHildaGreen2013C 5

Now I fill in Columns H to N with:

=TRIM(K1&" "&L1&" "&M1&" "&N1)=INDEX($A1:$F1,1,$G1-1)=INDEX($A1:$F1,1,$G1)=IF(COLUMN(A1)<=$G1-2,A1,"")=IF(COLUMN(B1)<=$G1-2,B1,"")=IF(COLUMN(C1)<=$G1-2,C1,"")=IF(COLUMN(D1)<=$G1-2,D1,"")

The trim in the first formula is to remove any trailing spaces from name entries that have less that the maximum 4 elements. There are spaces between the double quotes in the first entry above.

Copying these formulae down gives me the following in H1 to N3:

Mr A Smith2012AMrASmith Mrs B M Jones2012BMrsBMJonesMrs Hilda Green2013CMrsHildaGreen 

Columns H to J are what you want.

I hope the wrapping in this post isn't too confusing.

Hope this helps.

Best wishes,

Ian

Onion Reporting Software Ltd

Sage month end reporting in Excel

www.onionrs.co.uk

P.S. My results from your later set of data

Mr A SmithSMITH11Mrs A G J HornblowerHORNBLOWER2Ms A GreenGRE1

The Solution

Phew, here is the solution:

Col 1  Data
Col 2 =LEFT(A1,LEN(A1)-LEN(C1)-LEN(D1)-1)
Col 3   =LEFT(E1,LEN(E1)-LEN(D1)-1)
Col 4 =RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Col 5 =RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))

Example

Col 1 Mrs A G J Hornblower HORNBLOWER 21
Col 2 Mrs A G J Hornblower
Col 3 HORNBLOWER
Col 4 21
Col 5 Ignore - for workings only

and no hidden spaces.

Hope this helps - now really do need another coffee!

Mark

Which just goes to show that

with Excel there is often more than one way to skin the cat. 3 different solutions all giving the same answer!

Couldn't agree more

- the only small difference with my solution (and the reason I provided it for completeness) was that it doesn't require the use of the text to columns command which the other two do.

However, apart from that, it gives the same answer!

Mark

Find and replace (Ctrl+h)

Find: 2012

Replace with ;2012;

Repeat for 2013, etc

Data: Text to columns with the delimiter ;

That ought to do it

Neat solution

for CC's first example but unfortunately it won't work for CC's second example.

Oops

didn't read that far down :(