Extract data from middle of cell

**ajhat** · 09-23-2014, 09:56 AM

Hi,

I have a spreadsheet with over 10,000 rows that contain data that needs to be seperated. Each of the 10k rows look like this:

09197000 LAST, FIRST M. 01 10 01

The first part is an ID number, 09197000, which changes for each cell. I can use a =LEFT(A1, 8) function for this.
The last part is a date in YY MM DD format. I can use a =RIGHT(A1, 8) function for this.

The problem I run into is with the middle portion.
The middle portion is a name that varies in length for each cell. As far as I know, the MID function will not work as it asks for a specific number of characters, but this will change for each cell.

Any help is appreciated.

**AlKey** · 09-23-2014, 10:04 AM

You try this

You string is in A1

in B1 enter this formula

=TRIM(LEFT(A1,FIND(" ",A1)))

Now, in D1 use this one

=RIGHT(A1,8)

and in C1

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,""))