+ Reply to Thread
Results 1 to 5 of 5

need help with

  1. #1
    Registered User
    Join Date
    04-24-2008
    Posts
    1

    Unhappy need help with

    I need to remove both blank spaces and numbers from data (leaving only the letters). I have listed an example of the data that I need to have this application performed on. Please note that there are also spaces before each number. Could some one please provide a formula/macro that would do this task.
    Thanks Brent

    1 mlkkklltll tvfalltvgi cgsflplpka saaaliyddf etglngwgpr gpetveltte
    61 eaysgryslk vsgrtstwng pmvdktdvlt lgesyklgvy vkfvgdsysn eqrfslqlqy
    121 ndgagdvyqn iktatvykgt wtllegqltv pshakdvkiy vetefknsps pqdlmdfyid
    181 dftatpanlp eiekdipslk dvfagyfkvg gaatvaelap kpakelflkh ynsltfgnel
    241 kpesvldyda tiaymeangg dqvnpqitlr aarpllefak ehnipvrght lvwhsqtpdw
    301 ffrenysqde napwaskevm lqrlenyikn lmealateyp tvkfyawdvv neavdpntsd
    361 gmrtpgsnnk npgsslwmqt vgrdfivkaf eyarkyapad cklfyndyne yedrkcdfii
    421 eiltelkakg lvdgmgmqsh wvmdypsism feksirryaa lgleiqltel dirnpdnsqw
    481 alerqanryk elvtklvdlk keginitalv fwgitdatsw lggypllfda eykakpafya
    541 ivnsvpplpt eppvqvipgd vngdgrvnss dltlmkryll ksisdfptpe gkiaadlned
    601 gkvnstdlla lkklvlrel

  2. #2
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    Not the prettiest but will give you probably the fastest result

    Use the find replace function from the edit menu.

    "Search" 1 replace by (leave blank)
    "Search" 2 replace by (leave blank)
    ...
    "Search" (insert space) replace by (leave blank)
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    brent,

    Please read the forum rules and adjust your title to reflect your question better....

    then you can try:

    =SUBSTITUTE(MID(A1,FIND(" ",TRIM(A1))+1,255)," ","") where A1 contains first string.

    copied down
    Last edited by NBVC; 04-24-2008 at 04:15 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    Actually found a quicker way:

    Please Login or Register  to view this content.
    NOTE:
    Yes, the title should reflect the problem you are looking for because a lot of work is made in making the database searchable by other users.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    Here's how I would tackle it.
    First, I'm assuming this is the only data on your spreadsheet and it's in column A.
    In column B, I'd put =TRIM(A1) and copy that down. This removes the spaces before the numbers.

    Then I'd copy column B and paste special >values overtop of Column A (putting everything back in the original position except without those leading spaces)

    Third, I'd do a text to columns> delimited with Spaces checked. That should leave you with only numbers in column A
    Then delete the other columns with the jibberish.

    ChemistB
    Edit: Oops, I read this backwards. Nevermind.
    Last edited by ChemistB; 04-24-2008 at 05:12 PM.

+ 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