Extracting ID's from middle of record.HELP

[DShack]

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

 

[mjbeam]

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)

 

[DShack]

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</SPAN>

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

thanks

 

[mjbeam]

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

 

[DShack]

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</SPAN>

<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</SPAN>

Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 $43.84</SPAN>

Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 $72.51</SPAN>

Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 $120.91</SPAN>

Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12$77.43</SPAN>

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

 

[mjbeam]

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

 

[bschwartz]

This works with all of the examples you posted:

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

Cell Formulas
Range	Formula
B1	=LEFT(REPLACE(A1,1,FIND(0,A1)-1,""),FIND(" ",REPLACE(A1,1,FIND("0",A1)-1,""))-1)

 

[bschwartz]

@mjbeam, beat me to it!

 

[hiker95]

DShack,

How about a macro solution?

Sample raw data:

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

After the macro:

Excel 2007
	A	B
1	Grant, DeAngela S 00312128 Fri,Aug 22 8.067 0.517 8.58 8.07 $80.59	00312128
2	Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 $43.84	00324338
3	Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 $72.51	00317017
4	Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 $120.91	00130644
5	Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12$77.43	00309377
6	Moussaoui, Abdelmjid 00224827 Fri,Aug 22 8.500 8.50 8.50 $73.78	00224827
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
2. Open your NEW workbook
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.

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.

 

[hiker95]

mjbeam,

Your formula is missing the leading 0 (zero)?


bschwartz,

Nicely done - one for my archives - thanks.