Breaking up information in one cell into multiple cells

**NMen** · 03-01-2013, 11:36 AM

As of now there are no constants from cell A2 down to cell A10.

I want to take apart the information in A2 so that the year of the cell is posted in B2, the name is posted in C2, and the number is posted in D4

A2 is currently: 2003 Gregory Holmes Johnson 34
A3 is currently: 2005 Samuel Mitchell Joan Lee 12
A4 is currently: 2009 Christopher Nelson Clemens Hayward 24
A5 is currently: 2009 Lucas Johnson Manuel 38

and so on...

So to restate my question, how do I take apart A2, when it has no constants, so that the year in A2 is shown in B2, the full name in A2 is shown in C2, and the number is shown in D4. Any help would be greatly appreciated.

I am also currently operating on Microsoft Excel

**alansidman** · 03-01-2013, 11:55 AM

Question: Is the number at the end of the cell always going to be two digits?

**NMen** · 03-01-2013, 12:11 PM

No the numbers at the end aren't constant. Some numbers at the end are three digits, some are 5, and some include decimals.

**Pete_UK** · 03-01-2013, 11:57 AM

Put this in B2:

=--LEFT(A2,4)

if you want the year as a number - if not, then omit the --, which turns the text value into a numeric value.

You can have this formula in C2:

=MID(A2,6,LEN(A2)-8)

assuming your numbers at the end are always 2 digits.

Then in D4 (or should it be D2 ??), you can have this formula:

=--RIGHT(A2,2)

Hope this helps.

Pete

**Kevin UK** · 03-01-2013, 12:25 PM

Hi NMen

See if the following are of any use.

In B2: =--LEFT(A2,4)
In D2: =--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

Then in C2: =TRIM(SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,""))
And copy the formulas down.