Extracting ID's from middle of record.HELP

DShack

Board Regular
Joined
Jan 15, 2014
Messages
55
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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
55
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
55
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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Grant, DeAngela S 00312128 Fri,Aug 22 8.067 0.517 8.58 8.07 $80.59</td><td style="text-align: right;;">00312128</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 $43.84</td><td style="text-align: right;;">00324338</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 $72.51</td><td style="text-align: right;;">00317017</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 $120.91</td><td style="text-align: right;;">00130644</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12$77.43</td><td style="text-align: right;;">00309377</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">REPLACE(<font color="Red">A1,1,FIND(<font color="Green">0,A1</font>)-1,""</font>),FIND(<font color="Red">" ",REPLACE(<font color="Green">A1,1,FIND(<font color="Purple">"0",A1</font>)-1,""</font>)</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />
 

hiker95

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

How about a macro solution?

Sample raw data:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Grant, DeAngela S 00312128 Fri,Aug 22 8.067 0.517 8.58 8.07 $80.59</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 $43.84</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 $72.51</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 $120.91</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12$77.43</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;;">Moussaoui, Abdelmjid 00224827 Fri,Aug 22 8.500 8.50 8.50 $73.78</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Grant, DeAngela S 00312128 Fri,Aug 22 8.067 0.517 8.58 8.07 $80.59</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">00312128</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Gesner, Erick 00324338 Fri,Aug 22 5.250 0.500 5.75 5.25 $43.84</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">00324338</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Gilmour, Lindsay W 00317017 Sat,Aug 23 8.083 0.650 8.73 8.08 $72.51</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">00317017</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Gomez, Magaly 00130644 Fri,Aug 22 8.050 8.05 8.05 $120.91</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">00130644</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Gonzalez Ortiz, Maria R 00309377 Sun,Aug 24 8.117 0.500 8.62 8.12$77.43</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">00309377</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;;">Moussaoui, Abdelmjid 00224827 Fri,Aug 22 8.500 8.50 8.50 $73.78</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">00224827</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,750
Messages
5,638,138
Members
417,010
Latest member
jnuss03

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