+ Reply to Thread
Results 1 to 11 of 11

automatically removing letters from a cell and keeping only the numbers

  1. 08-26-2013, 02:05 PM #1
    jazpc
    jazpc is offline
    Registered User
    Join Date
    08-26-2013
    Location
    ontario
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    5

    automatically removing letters from a cell and keeping only the numbers

    New to the forum...hoping to get some help with something

    At work i need to export data into excel and then go about formatting columns A-Z with specific requirements for each...sometimes there are 100 lines in each column.

    EXCEL.jpg

    In the image above...the left 2 columns (A,B) are kind of what it looks like when i first export the data to excel...then the other 2 columns on the right, (D,E) are an example of what i have to make them look like...basically remove all letters and keep only the numbers.
    I've been doing this manually by going through each cell one by one and just removing the letters...and its taking so much time when there are a lot of rows to edit.....is there an easier way to do this?
    Please help!

    JM
    Reply With QuoteRegister To Reply
  2. 08-26-2013, 02:32 PM #2
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: automatically removing letters from a cell and keeping only the numbers

    =LEFT(b1,FIND(" ",b1)-1) write this in column E and drag
    Reply With QuoteRegister To Reply
  3. 08-26-2013, 02:40 PM #3
    jazpc
    jazpc is offline
    Registered User
    Join Date
    08-26-2013
    Location
    ontario
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    5

    Re: automatically removing letters from a cell and keeping only the numbers

    Sorry...im pretty new to the formula part of excel....i dont understand what you mean exactly

    JM
    Reply With QuoteRegister To Reply
  4. 08-26-2013, 02:44 PM #4
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: automatically removing letters from a cell and keeping only the numbers

    copy above formula in column E1 and ctrl + enter move your mouse to the lower right hand corner of column e1 you will find + sign then drag the same formula from top to bottom! working now for first column !!
    Reply With QuoteRegister To Reply
  5. 08-26-2013, 02:50 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,945

    Re: automatically removing letters from a cell and keeping only the numbers

    Hi and welcome to the forum

    Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  6. 08-26-2013, 03:13 PM #6
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: automatically removing letters from a cell and keeping only the numbers

    Jazpc, in column D copy and paste =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)) and in column E copy and paste =LEFT(b1,FIND(" ",b1)-1) then just drag.
    Reply With QuoteRegister To Reply
  7. 08-26-2013, 03:26 PM #7
    jazpc
    jazpc is offline
    Registered User
    Join Date
    08-26-2013
    Location
    ontario
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    5

    Re: automatically removing letters from a cell and keeping only the numbers

    I am doing that...I am pasting into D1 and E1 right? Then where am I supposed to drag...nothing happens when i drag it...ill upload the file like FDibbins suggested
    Reply With QuoteRegister To Reply
  8. 08-26-2013, 03:28 PM #8
    jazpc
    jazpc is offline
    Registered User
    Join Date
    08-26-2013
    Location
    ontario
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    5

    Re: automatically removing letters from a cell and keeping only the numbers

    here is the file...maybe im missing something

    Book1.xlsx
    Reply With QuoteRegister To Reply
  9. 08-26-2013, 03:46 PM #9
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: automatically removing letters from a cell and keeping only the numbers

    Hello Jazpc find the attached file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 08-26-2013, 04:04 PM #10
    jazpc
    jazpc is offline
    Registered User
    Join Date
    08-26-2013
    Location
    ontario
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    5

    Re: automatically removing letters from a cell and keeping only the numbers

    ok...i think i might be a bit too new for this stuff...from my example i uploaded...D and E are actually A and B...just what i am hoping for them to actually look like without editing each cell one by one...the data i export to excel creates a spreadsheet with 26 columns a to z...each column/row has specific data in them....these are the two that take forever to edit i uploaded it again without the other 2 columns....

    book2.xlsx
    Reply With QuoteRegister To Reply
  11. 08-26-2013, 04:25 PM #11
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: automatically removing letters from a cell and keeping only the numbers

    Hi jazpc,

    Here's a little VBA routine:

    Start it with the first data containing cell selected

    Please Login or Register  to view this content.
    Directions for running the routine(s) just supplied

    Copy the code to the clipboard

    Press ALT + F11 to open the Visual Basic Editor.

    Select “Module” from the Insert menu

    Type "Option Explicit" then paste the code under it

    And, you should be ready to go

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    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. Removing leading numbers, letters, full stop, and space
    By Hambone70 in forum Excel General
    Replies: 2
    Last Post: 02-16-2011, 01:09 PM
  2. How to Make Cells Automatically Translate Numbers and Letters?
    By imexcelling in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-10-2010, 02:15 PM
  3. Removing all text except numbers and letters
    By stoopkid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-04-2007, 11:25 PM
  4. [SOLVED] Removing characters from a cell (keeping only the numbers)
    By Monk in forum Excel General
    Replies: 9
    Last Post: 01-24-2006, 11:35 AM
  5. Removing characters from a cell (keeping only numbers) 2
    By Monk in forum Excel General
    Replies: 6
    Last Post: 01-24-2006, 10:50 AM

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