+ Reply to Thread
Results 1 to 7 of 7

Separating numbers from words in cells in large data file

Hybrid View

  1. 04-07-2015, 07:59 AM #1
    cbarth
    cbarth is offline
    Registered User
    Join Date
    01-14-2015
    Location
    Glyndon, Minnsota
    MS-Off Ver
    Microsoft Office 365
    Posts
    11

    Separating numbers from words in cells in large data file

    Good morning - I have a large data file and I would like to create a formula to separate the following data. The first set of data seems to be always 11 characters long so I am using the =left(a2,11) formula. I know there is a formula I can use to full the names from the other side of it by but I can't remember the exact way it should be. I have pasted a sample of the data below.

    D1503117794DIEDRE
    D1386762710LISA
    D1504640128JUANITA
    D1504642086SYLVANUS
    D1289483319OLIVIA
    D1502060609SHERRY
    D1504585081BERNADETTE
    D1503117244SAMANTHA

    I was looking through the forum and thought I could use the right and substitute formula but I keep getting and error on the way I have that formula written.

    Any help you can give me on this would be greatly appreciated. THANK YOU!!!!

    ON ANOTHER NOTE:

    This data stems from a text file that I have to convert using text to column using the space option and then take the time to move all the data to the correct columns. Is there a simpler way to manipulate this text data from the get go? A sample

    D1504987614DONALD SDUCK F P O Box 112 DISNEY MD55555 USA 20150501197410082012010920120504 N17.040 Chemical Dependency Counseling NY 1000778 M YNYOY 6052680971 N
    D1504640155MICKEY LMOUSE W20120306P O Box 374 DISNEY MD55555 USA 196108182012010920120504 N13.120 Gen Studies Elem. Teach Opt. NY 1002074 L NNYOY 6052681769 Y131202201002002000Y 20110601NW20110601
    D1504640196SCOOBY SDOO W2012031440 HATLE DRIVE DISNEY MD55555 USA 195908052012010920120504 N06.040 Business Administration NY 1000926 M NNYOY 6059279109 Y060401201002002000Y 19960110NW20120109
    D1504889428THUNDER AROAD L 5 1/2 Maple Street DISNEY MD55555 USA 20130501196007142012010920120504 N NN 1003364 A NN OY 6052680270 Y 201099000000 20120109NL20120109
    D1504802789BEAUTY ABEAST W20120314P O Box 902 DISNEY MD55555 USA 197807102012010920120504 N13.120 Gen Studies Elem. Teach Opt. NY 1000972 rkampeska@esds.edu M NNYOY 6059241523 Y131202201003002000Y 19950821NW19950821
    D1289483319PRINCE HCHARMING JOHNSON F 44455 SD HWY 10 DISNEY MD55555 USA 20150501194504052012010920120504 N13.120 Gen Studies Elem. Teach Opt. NY 1001108 M NN OY 6052680581 Y131202201002002000Y 19990120NF19990120
    D1504862675PATRICK WDUCK F 605 Lydia Goodsell Street DISNEY MD55555 USA 20150501197204172012010920120504 N06.040 Business Administration NY 1001119 G. NNYOY 6056984070 Y060401201002002000Y 20000119NF20000119
    Reply With QuoteRegister To Reply
  2. 04-07-2015, 08:29 AM #2
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Separating numbers from words in cells in large data file

    try:
    =MAX(IFERROR(--MID($A1,ROW($A$1:$A$20),10),""))
    with CSE (control+shift+enter)
    where $A1 - your data
    $A$1:$A$20 - range of your data
    ... and drag it down


    names:
    first non-numeric character, after numeric string, is on position 12 (without very first character = D), so
    =MID(A1,12,LEN(A1))

    I assume: always 1 letter, 10 numbers and the rest
    or
    names (long strings)
    =MID(A1,12,LEN(LEFT(A1,FIND(CHAR(32),A1)))-11)
    Last edited by sandy666; 04-07-2015 at 08:40 PM.
    Reply With QuoteRegister To Reply
  3. 04-07-2015, 08:45 AM #3
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Separating numbers from words in cells in large data file

    please attach sample excel file with desired result
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  4. 04-07-2015, 09:00 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Separating numbers from words in cells in large data file

    Here's one way (assuming that the number is prefixed with a single letter character.).
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  5. 04-07-2015, 09:38 AM #5
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Separating numbers from words in cells in large data file

    can use
    =MID(A1,IFERROR(MATCH(99,INDEX(MID(A1,ROW($1:$256),1)+0,0)),0)+1,256)
    or
    =MID(A1,12,256)
    Reply With QuoteRegister To Reply
  6. 04-07-2015, 09:40 AM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Separating numbers from words in cells in large data file

    Since there is no clear example of what the desired output should be, I would offer this:

    Enter formula in B1 and pull it to the right and then down

    =TRIM(MID(SUBSTITUTE("|"&REPLACE(TRIM(LEFT(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*2)),12,,"|"),"|",REPT(" ",LEN($A1))),LEN($A1)*COLUMNS($A:A),LEN($A1)))


    Row\Col
    A
    B
    C
    1
    		 D1504987614DONALD SDUCK F P O Box 112 DISNEY MD55555 USA 20150501197410082012010920120504 N17.040 D1504987614 DONALD SDUCK
    2
    		 D1504640155MICKEY LMOUSE W20120306P O Box 374 DISNEY MD55555 USA 196108182012010920120504 D1504640155 MICKEY LMOUSE
    3
    		 D1504640196SCOOBY SDOO W2012031440 HATLE DRIVE DISNEY MD55555 USA 195908052012010920120504 N06.040 D1504640196 SCOOBY SDOO
    4
    		 D1504889428THUNDER AROAD L 5 1/2 Maple Street DISNEY MD55555 USA D1504889428 THUNDER AROAD
    5
    		 D1504802789BEAUTY ABEAST W20120314P O Box 902 DISNEY MD55555 USA 197807102012010920120504 D1504802789 BEAUTY ABEAST
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  7. 04-08-2015, 07:06 AM #7
    cbarth
    cbarth is offline
    Registered User
    Join Date
    01-14-2015
    Location
    Glyndon, Minnsota
    MS-Off Ver
    Microsoft Office 365
    Posts
    11

    Re: Separating numbers from words in cells in large data file

    Thank you everyone. I have attached an file as requested.

    The one tab has my imported data. Another tab has the data after I have completed the text to column data conversion. The third tab has my desired results. As you can see there is not always data in each field so I hope that doesn't cause a problem.

    Thanks for you help!

    Have a great day!!!
    Attached Files Attached Files
    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. [SOLVED] Separating words in one cell into many cells
    By excel is cool in forum Excel General
    Replies: 5
    Last Post: 06-12-2014, 11:58 AM
  2. Data Cleansing- Separating 2 words
    By nav505 in forum Excel General
    Replies: 7
    Last Post: 02-12-2013, 03:57 PM
  3. [SOLVED] Separating names from numbers and searching for key words - help
    By percyth1 in forum Excel General
    Replies: 4
    Last Post: 03-28-2012, 12:26 AM
  4. [SOLVED] Separating a column by numbers vs. letters when numbers are at the end of the cells?
    By Skywalker in forum Excel General
    Replies: 4
    Last Post: 02-04-2012, 01:46 PM
  5. What is the fucntion for separating words into different cells?
    By pivich in forum Excel General
    Replies: 2
    Last Post: 10-10-2007, 09:15 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