# Extracting ID's from middle of record.HELP

#### DShack

##### Board Regular
Hello Everyone,
I'm trying to create a formula that extracts the employee id number out from the below record. The id is in bold. I have tried using left, mid function but have been unsuccessfull in doing so. any help would be greatly apprepriated.

Moussaoui, Abdelmjid 00224827 Fri,Aug 22 8.500 8.50 8.50 \$73.78

Thanks

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Assuming that value was in cell A1:

=MID(MID(MID(A1,FIND(" ",A1)+1,LEN(A1)),FIND(" ",MID(A1,FIND(" ",A1)+1,LEN(A1)))+1,LEN(A1)),1,FIND(" ",MID(MID(A1,FIND(" ",A1)+1,LEN(A1)),FIND(" ",MID(A1,FIND(" ",A1)+1,LEN(A1)))+1,LEN(A1)))-1)

Thank you very much. Can you help me with this as well. I'm trying to extract the hours directly after the word Total:

 Total: 40.000 2.933 0.000 0.000 0.000 42.93 42.93 \$385.39

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

thanks

=mid(mid(a1,find(" ",a1)+1,len(a1)),1,find(" ",mid(a1,find(" ",a1)+1,len(a1)))-1)

What happens when i have an employee with a middle name for example.
 Langston, Alison B 00266600 Sun,Aug 24 8.000 8.00 8.00 \$101.20

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

How would i extract the id from this data. The data set that i have has several items in one column. see below and i need to extract the id out of each one.

 Grant, DeAngela S 00312128 Fri,Aug 22 8.067 0.517 8.58 8.07 \$80.59 Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 \$43.84 Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 \$72.51 Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 \$120.91 Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12\$77.43

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

=mid(mid(a1,find(" 0",a1)+2,len(a1)),1,find(" ",mid(a1,find(" 0",a1)+2,len(a1)))-1)

This works with all of the examples you posted:

Excel 2010
AB
1Grant, DeAngela S 00312128 Fri,Aug 22 8.067 0.517 8.58 8.07 \$80.5900312128
2Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 \$43.8400324338
3Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 \$72.5100317017
4Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 \$120.9100130644
5Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12\$77.4300309377
Sheet3
Cell Formulas
RangeFormula
B1=LEFT(REPLACE(A1,1,FIND(0,A1)-1,""),FIND(" ",REPLACE(A1,1,FIND("0",A1)-1,""))-1)

@mjbeam, beat me to it!

DShack,

Sample raw data:

Excel 2007
AB
1Grant, DeAngela S 00312128 Fri,Aug 22 8.067 0.517 8.58 8.07 \$80.59
2Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 \$43.84
3Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 \$72.51
4Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 \$120.91
5Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12\$77.43
6Moussaoui, Abdelmjid 00224827 Fri,Aug 22 8.500 8.50 8.50 \$73.78
7
Sheet1

After the macro:

Excel 2007
AB
1Grant, DeAngela S 00312128 Fri,Aug 22 8.067 0.517 8.58 8.07 \$80.5900312128
2Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 \$43.8400324338
3Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 \$72.5100317017
4Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 \$120.9100130644
5Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12\$77.4300309377
6Moussaoui, Abdelmjid 00224827 Fri,Aug 22 8.500 8.50 8.50 \$73.7800224827
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
``````Sub ExtractID()
' hiker95, 09/03/2014, ME803119
Dim c As Range, s, i As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
s = Split(c, " ")
For i = LBound(s) To UBound(s)
If s(i) Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]" Then
With c.Offset(, 1)
.NumberFormat = "@"
.Value = s(i)
End With
Exit For
End If
Next i
Next c
Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub``````

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractID macro.

mjbeam,

bschwartz,

Nicely done - one for my archives - thanks.

Replies
17
Views
282
Replies
4
Views
588
Replies
3
Views
681
Replies
0
Views
272
Replies
15
Views
2K

1,219,798
Messages
6,150,318
Members
450,951
Latest member
kh198

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back