Pulling a specific text/number from a cell

[InvasiveZero]

Hi Everyone,

I am looking for either a formula or code that will allow me to pull a "project code" from a string of text in a cell. The text in each cell is not a consistent length. However, the project codes in the Text string are always started with "A" Followed by 4 number digits ex. "A1234". Below is a picture of a few cells where I am trying to extract the project code. Currently I am dealing with around 3000+ lines and the manual process is out of the question. LOL

[TABLE="width: 1006"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CC_CUNNINGHAM_DARA S_589174_A0854 ? HWOW EVALUATIVE TESTING__USER RESEARCH RECORDING SOFTWARE FOR IPAD_LOOKBACK PRO_08112017____05555[/TD]
[/TR]
[TR]
[TD]CC_CUNNINGHAM_DARA S_589174_A0854 ? HWOW EVALUATIVE TESTING__TRANSCRIPTION SERVICE_REV COM_08112017____05555[/TD]
[/TR]
[TR]
[TD]CC_CUNNINGHAM_DARA S_589174_A0854 ? CWOW EVALUATIVE TESTING__PARKING_PARKINGMETER7 8772427901_08092017____05555[/TD]
[/TR]
[TR]
[TD]CC_CUNNINGHAM_DARA S_589174_A0854 ? CWOW EVALUATIVE TESTING__RENTAL CAR FUEL_SAJ POWER_08092017____05555[/TD]
[/TR]
[TR]
[TD]CC_CUNNINGHAM_DARA S_589174_A0854 ? CWOW EVALUATIVE TESTING__RENTAL CAR FUEL CREDIT_SAJ POWER_08092017____05555[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF Rx Project (missed loc code in original recls) IT ANAPLAN RECLASS OF A1638_0.5_0_COLETTE_HUMPHREY_8/13/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF Rx Project (missed loc code in original recls) IT ANAPLAN RECLASS OF A1638_1_0_COLETTE_HUMPHREY_8/13/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF Rx Project (missed loc code in original recls) IT ANAPLAN RECLASS OF A1638_1.5_0_COLETTE_HUMPHREY_8/13/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF Rx Project (missed loc code in original recls) IT ANAPLAN RECLASS OF A1638_1_0_COLETTE_HUMPHREY_8/6/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF ROVR OPEX A0858_23.54_AUTO MILEAGE___9/24/2017_INTEGRO INCORPORATED[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Data Ctr Project 9007908-154 A1332_53.5_AUTO MILEAGE_PEGGY_PANGERSIS_8/20/2017_TECH USA, LLC[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF ROVR OPEX A0858_25.15_AUTO MILEAGE___8/20/2017_INTEGRO INCORPORATED[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Data Ctr Project 9007908-154 A1332_8_0_CYNTHIA_BROWN_9/3/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Data Ctr Project 9007908-154 A1332_8_0_CYNTHIA_BROWN_9/24/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Data Ctr Project 9007908-154 A1332_8_0_CYNTHIA_BROWN_9/17/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Project A1277_6_0_KHALID_ROSA_9/10/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Project A1277_6_0_KHALID_ROSA_9/3/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Data Ctr Project 9007908-154 A1332_8_0_CYNTHIA_BROWN_9/10/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Project A1277_4_0_KHALID_ROSA_8/27/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Project A1277_6_0_KHALID_ROSA_9/24/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Project A1539_8_0_RAJESH_GANGONE_9/10/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Data Ctr Project 9007908-154 A1332_8_0_CYNTHIA_BROWN_8/27/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
[TR]
[TD]SEP17 IT ANAPLAN RECLASS OF DMG Project A1277_7_0_KHALID_ROSA_9/3/2017_APEX SYSTEMS, LLC (562)[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,

InvasiveZero


[TABLE="width: 1006"]
<tbody>[TR]
[TD="class: xl64, width: 1006"][TABLE="width: 1006"]
<tbody>[TR]
[TD="class: xl66, width: 1006"][TABLE="width: 1006"]
<tbody></tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sektor]

Re: Need Help pulling a specific text/number from a cell

UDF (user-defined formula) - use it like regular formula:

Function GetCode(cell)
    With CreateObject("VBScript.RegExp")
        .Pattern = "A\d{4}"
        With .Execute(cell)
            GetCode = IIf(.Count > 0, .Item(0), CVErr(xlErrNA))
        End With
    End With
End Function

 

[InvasiveZero]

Re: Need Help pulling a specific text/number from a cell

Thank you so much! Life Saver! I am so grateful!

 

[Sektor]

Re: Need Help pulling a specific text/number from a cell