+ Reply to Thread
Results 1 to 4 of 4

text to column help need for inconsistent seperator

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-14-2005
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    172

    text to column help need for inconsistent seperator

    I have the data below in column A, I need to put the numbers in column A and the description in the column B. Since the numbers are not consistent in length I can not just put a line where I want the column B to start. Is there a way to do this with Text to Column or would I need to create a macro or something.

    10 Capital and reserves1
    101 Capital1
    1011 Subscribed capital not paid
    1012 Subscribed capital paid
    1015 Patrimony (autonomous public sector entities)
    1016 Public patrimony2
    104 Premium related to capital
    1041 Share premium
    1042 Merger premium
    1043 Share premium contribution in kind
    1044 Debenture conversion premium
    105 Revaluation reserve
    106 Reserves
    1061 Legal reserve
    1063 Statutory or contractual capital reserve
    1064 Fair value reserve3
    1065 Reserve representing the revaluation reserve surplus
    1067 Reserves from foreign exchange differences in respect to a net investment in a foreign entity3
    1068 Other reserves
    107 Conversion reserve4
    108 Minority interest5
    1081 Minority interest - profit (loss) for the period
    1082 Minority interests – other own capital
    109 Own shares
    1091 Short-term own shares
    1092 Long-term own shares
    1095 Shares of the absorbing entity owned by the entity absorbed
    11 Retained earnings
    117 Retained earnings
    1171 Profit/loss carried forward

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: text to column help need for inconsistent seperator

    if A1 is the cell reference
    first code for number

    =LEFT(A1,FIND(" ",A1,1))
    2nd for text
    =TRIM(MID(A1,FIND(" ",A1,1)+1,100))
    assuming 100 is the max...can just put any number in there you like
    Last edited by humdingaling; 06-03-2013 at 12:11 AM. Reason: updated from 80 to 100 to fit all sample

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,336

    Re: text to column help need for inconsistent seperator

    Hi Mile,

    Find the attached that uses a Left and Mid formula to separate the two parts for you.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Contributor
    Join Date
    03-14-2005
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    172

    Re: text to column help need for inconsistent seperator

    awesome, both work, thanks guys!

+ 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