+ Reply to Thread
Results 1 to 11 of 11

Macro to clean data

  1. #1
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Macro to clean data

    Hi everyone,

    I have some data like this in cells a1:a1000. What I need to do is to clean the data so that the grid reference is removed. The grid reference can be anything from the first 1 to the first 5 characters. The problem I have is getting it to recognise the number of characters so it knows how many to delete. Any ideas.

    Z2 - Behind Cropston Ave
    D20 - Barlestone Parish
    C3b - Field between Barton Road and Poplar Terrace, Congerstone
    B711 - Bird Field
    C2 - Bodmin open space
    C17 - Manor Road

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to clean data

    Is there always a dash between the grid reference and the rest of the text?

    Also, is this a one-off job, or something you have to do regularly?

  3. #3
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: Macro to clean data

    Hi Andrew,

    Wow that was quick! Thank you.

    I'm afraid that the dash is not always there. It is on the majority though. This will just be a one-off as well.

  4. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to clean data

    OK, will there always be a space between the grid reference and the main text?

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Macro to clean data

    Try this
    Please Login or Register  to view this content.
    Drag/Fill Down
    Last edited by Marcol; 10-03-2011 at 11:12 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to clean data

    I thought we might actually determine the nature of the OP's data before we dived in with formula, but your way is good as well

  7. #7
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: Macro to clean data

    Thanks both. That works absolutely flawlessly!! I have left you both rep. Can I ask, for my own edification, a simple explanation of how the formula works?

    Thanks peeps!!

  8. #8
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro to clean data

    The first IF statement checks to see if the cell is blank, if it is then it returns a blank value.

    If the cell has contents then it checks to see if trying to find a "-" in the cell value returns an error.

    If it does return an error (i.e. the value doesn't contain a dash) then it returns the cell contents starting from the point after the first space in the cell (e.g. it converts "Z123 Example" into "Example".

    If there is a dash in the value then it returns the contents of the cell after the " - " (i.e. converts "Z123 - Example" into "Example".

    You could also try =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",1+NOT(ISERROR(FIND("-",A1)))))+1,LEN(A1))

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Macro to clean data

    Thanks crocodile shoes for the explanation, my cranial matter is somewhat submerged in pints of dog at the moment ... ...

    On behalf of all, happy to have helped.

  10. #10
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: Macro to clean data

    Thanks all

  11. #11
    Registered User
    Join Date
    01-01-2014
    Location
    Astoria, OR
    MS-Off Ver
    Excel for Mac 2011
    Posts
    3

    Re: Macro to clean data

    Quote Originally Posted by Andrew-R View Post
    I thought we might actually determine the nature of the OP's data before we dived in with formula, but your way is good as well
    Just for fun - looks to me like an old census record from England.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1