Extracting ID's from middle of record.HELP

DShack

Board Regular
Joined
Jan 15, 2014
Messages
62
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
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

Board Regular
Joined
Jan 15, 2014
Messages
62
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Apr 6, 2002
Messages
756
=mid(mid(a1,find(" ",a1)+1,len(a1)),1,find(" ",mid(a1,find(" ",a1)+1,len(a1)))-1)
 

DShack

Board Regular
Joined
Jan 15, 2014
Messages
62
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 6, 2002
Messages
756
=mid(mid(a1,find(" 0",a1)+2,len(a1)),1,find(" ",mid(a1,find(" 0",a1)+2,len(a1)))-1)
 

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367

ADVERTISEMENT

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)
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
DShack,

How about a macro solution?

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
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.

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.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
mjbeam,

Your formula is missing the leading 0 (zero)?


bschwartz,

Nicely done - one for my archives - thanks.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,579
Messages
5,838,201
Members
430,534
Latest member
chacc

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top