+ Reply to Thread
Results 1 to 10 of 10

Clensing data help needed!

Hybrid View

  1. 08-01-2014, 08:51 AM #1
    aledrhys666
    aledrhys666 is offline
    Registered User
    Join Date
    07-10-2013
    Location
    holyhead
    MS-Off Ver
    Excel 2007
    Posts
    9

    Clensing data help needed!

    Hey, Ive just queried the database at work to gather some data. I need to get some code numbers out of the cells in order to reference the new replacement codes. Here is some raw data from the database -

    1. OBTAIN PERMIT FOR WORK & WORK IN ACCORDANCE WITH PIMI M2601 006<br/>2. OBTAIN HAND TORCHES (HIGH POWERED TORCHES AVAILABLE FROM S/WARDEN)<br/>3. OBTAIN WADERS, WELLINGTONS, SHOVELS, PLASTIC BAGS, 2 OFF 6' SCAFF BOARDS & TRUCK<br/>4. FOUR MEN TO ENTER DTV MANDOOR WITH CARE (VERY SLIPPERY)<br/>5. ONE MAN TO REMAIN OUTSIDE DTV MANDOOR AS SAFEGUARD<br/>6. TAKING CARE PROCEED ALONG STEEL SECTION APPROX. 70M TO SUMPS<br/>7. PLACE SCAFFOLDING BOARDS ACCROSS SUMP AS A 'BRIDGE'<br/>8. REMOVE ALL DEBRIS FROM THE THREE SUMPS<br/>9. PROCEED TO PAIRED UNIT AND REMOVE ALL DEBRIS FROM THE THREE SUMPS<br/>10. EXIT THE DTV MANDOOR ENSURING ALL EQUIPMENT REMOVED.<br/>11. INFORM PME WHEN COMPLETED SO THAT DEBRIS CAN BE INSPECTED.<br/>12. INFORM SHIFT MANAGER WHEN COMPLETED FOR FINAL INSPECTION.<!-- RICH TEXT -->

    All I need is the number - M2601 006
    Is there any formula that would allow me just to extract those numbers. Because I have over 2000 of them to do. Would be a great help.
    Thanks.
    Reply With QuoteRegister To Reply
  2. 08-01-2014, 09:04 AM #2
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Clensing data help needed!

    Is the number always the last text on item 1? is it always preceded by PIMI?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...
    Reply With QuoteRegister To Reply
  3. 08-01-2014, 09:06 AM #3
    aledrhys666
    aledrhys666 is offline
    Registered User
    Join Date
    07-10-2013
    Location
    holyhead
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Clensing data help needed!

    No that's the problem, as there have been many content contributors, there is so much variant in how they look. Nobody thought when creating the table to put a format control on the column.
    Reply With QuoteRegister To Reply
  4. 08-01-2014, 09:10 AM #4
    boopathiraja
    boopathiraja is offline
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Clensing data help needed!

    Try
    =RIGHT(LEFT(B1,FIND("<",B1,1)-1),9)
    ☚ Click ★ just below left if it helps, Boo?ath?
    Reply With QuoteRegister To Reply
  5. 08-01-2014, 09:45 AM #5
    aledrhys666
    aledrhys666 is offline
    Registered User
    Join Date
    07-10-2013
    Location
    holyhead
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Clensing data help needed!

    No still nothing. I'm guessing that it looks for the </br> tags? That wont work because it just picks up the first <br> statement in the paragraph. Thanks for the help tho!
    Reply With QuoteRegister To Reply
  6. 08-01-2014, 09:53 AM #6
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Clensing data help needed!

    Will all the 'numbers' you are trying to find follow the same pattern: "A0000 000" ?

    If so, then try the following UDF:
    Function FindNum(stInput As String) As String
Dim i As Integer, stTest As String
For i = 1 To Len(stInput) - 1
    stTest = Mid(stInput, i, 9)
    If stTest Like "[A-Za-z]#### ###" Then FindNum = stTest
Next i
End Function
    use as:
    Formula: copy to clipboard 
    =FindNum(A1)
    Reply With QuoteRegister To Reply
  7. 08-01-2014, 10:08 AM #7
    aledrhys666
    aledrhys666 is offline
    Registered User
    Join Date
    07-10-2013
    Location
    holyhead
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Clensing data help needed!

    I'm getting a #name? error when I run the code? any ideas?
    Reply With QuoteRegister To Reply
  8. 08-01-2014, 10:17 AM #8
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Clensing data help needed!

    You pasted the code into a standard module, right - not the workbook or worksheet module...
    Reply With QuoteRegister To Reply
  9. 08-01-2014, 10:20 AM #9
    aledrhys666
    aledrhys666 is offline
    Registered User
    Join Date
    07-10-2013
    Location
    holyhead
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Clensing data help needed!

    Working now. (sorry about the noob mistake). Thanks for the help!
    Reply With QuoteRegister To Reply
  10. 08-01-2014, 10:26 AM #10
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Clensing data help needed!

    No need to apologise! It's how we learn Happy to help, thanks for the feedback.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Is their a way to remove the data from a custom for fast and keep the needed data?
    By luckyind in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-07-2014, 11:29 PM
  2. Data Validation - List of invalid data needed
    By dlast in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2013, 08:27 AM
  3. [SOLVED] Help to Chart Comparison Data of 2 sets of data with 5 variables each needed please
    By bluejasmine in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-13-2012, 12:45 PM
  4. Excel 2007 : Formula needed to check if cell data DOESN'T match data in list
    By dancing-shadow in forum Excel General
    Replies: 11
    Last Post: 02-10-2011, 08:13 AM
  5. UPDATED - Clensing data using a macro and separating already processed data
    By AK262007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2009, 03:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1