+ Reply to Thread
Results 1 to 12 of 12

Separating text & numbers in different columns

  1. #1
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    6

    Separating text & numbers in different columns

    Hi,

    I have to separate text & numbers in different columns.

    e.g. 349,910 American Capital Ltd. (a) 4,902,239

    i want all 3 data in separate columns, there is a series of data like this wherein numbers are there both at begining & end & text in middle. Can you please suggest on this.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,888

    Re: Separating text & numbers in different columns

    Show some more examples of the data that you have, or better still - attach a sample workbook.

    Pete

  3. #3
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Separating text & numbers in different columns

    Hi,

    I am attaching a file. Please refer to it as an example. There are thousands of entries like this.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Separating text & numbers in different columns

    Just to add to my previous post, i am attaching 1 more file in which i have shown how i want the final entry. So you can refer this as the latest one.
    Attached Files Attached Files

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Separating text & numbers in different columns

    Use Text To Columns

    Data>>Data Tools>>Text To Columns


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,888

    Re: Separating text & numbers in different columns

    See attached file.

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Separating text & numbers in different columns

    Thanks a lot for your help, it is working great. But din't got the formula. Please explain me the 3 formulae.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,888

    Re: Separating text & numbers in different columns

    The first formula extracts the characters up to the first space, sees if that is a number and if it is it returns it as a number, otherwise it returns an empty cell. The third formula does the same, though it looks at the characters after the last space. The middle formula removes both the first number and the last number from the string, and then removes any leading or trailing spaces.

    Hope this helps.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  9. #9
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Separating text & numbers in different columns

    Hi Pete,

    The 3 formulae you gave me earlier are working great, however there is another kind of data in which i need help. Can you please help me with the attached data. Problem is same breaking text & numbers into different columns. Please use Separating text & columns file.
    Attached Files Attached Files

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,888

    Re: Separating text & numbers in different columns

    I can't help you on this, as I'm just about to go away for a few days - back Friday evening.

    Pete

  11. #11
    Registered User
    Join Date
    01-27-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Separating text & numbers in different columns

    Hi Pete,

    Can you help me with this data by separating all 5 data types in 5 different columns.
    Attaching file for your reference.

    Thanks,

    Shikha
    Attached Files Attached Files

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,888

    Re: Separating text & numbers in different columns

    See attached file, which parses the data and bunches the records up.

    Your data is fixed width, so you could easily have used text-to-columns, and then have a bit of manipulation afterwards.

    As this did not follow on directly from your earlier postings, you should have started a new thread.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ Reply to 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 Binary Numbers via "Text to Columns" Type Approach
    By JP777 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-22-2012, 09:19 AM
  2. Separating numbers from text
    By njmania25 in forum Excel General
    Replies: 4
    Last Post: 09-10-2011, 01:25 PM
  3. Excel 2007 : Need help separating numbers/text
    By dancediva in forum Excel General
    Replies: 6
    Last Post: 04-27-2011, 06:14 PM
  4. Separating numbers into multiple columns
    By MarginofBuffett in forum Excel General
    Replies: 2
    Last Post: 02-18-2011, 03:45 PM
  5. Separating text from numbers
    By shadow91 in forum Excel General
    Replies: 4
    Last Post: 03-19-2010, 10:10 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1