+ Reply to Thread
Results 1 to 5 of 5

Split cell value based on number, after every number appereance in cell

  1. #1
    Registered User
    Join Date
    05-12-2021
    Location
    Europe
    MS-Off Ver
    365
    Posts
    45

    Split cell value based on number, after every number appereance in cell

    Hello everyone,
    I need to split the text from a cell that contains numbers after every appereancesugge of a number.
    In cell A1 i have this text : "1515 Provizioane pentru pensii şi obligaţii similare 1516 Provizioane pentru impozite"
    I need to split it in 2 cells : first cell value "1515 Provizioane pentru pensii şi obligaţii similare", second cell value "1516 Provizioane pentru impozite"
    This is a chart of accounts data, that means for every number a have a corresponding text.
    It can be multiple instances of this pattern in a cell, not only 2.
    I wrote a formula that extracts the text based on the final number, but that doesnt help me.
    I also wrote a formula to extract only the numbers from a cell, and one that extract their position in the cell (1515 would be 1, 1516 would be 8).

    I attached an example file with data and my formulas and the desired result.

    Any type or help or suggestion is welcomed.

    Thank you very much.
    Attached Files Attached Files
    "Good Morning, Good Evening and Good Night"

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Split cell value based on number, after every number appereance in cell

    one possible option, albeit a little brute force

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    if you have specific numeric ranges you can remove anything that isn't necessary (e.g. if no 9# accounts, remove the 9 SUBSTITUTE etc)
    added attachment such that it auto translates etc (above assumed ; delimiter etc)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-12-2021
    Location
    Europe
    MS-Off Ver
    365
    Posts
    45

    Re: Split cell value based on number, after every number appereance in cell

    Interesting approach,
    Thank you very much, this is perfect for what i need, i dont mind the "brute force"

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,213

    Re: Split cell value based on number, after every number appereance in cell

    Please try

    =LET(z;A2;s;SEQUENCE(;LEN(z));y;(MID(" "&z;s;1)=" ")*ISNUMBER(--MID(z;s;1));x;FILTER(s;y);MID(z;x;IFERROR(SMALL(x;SEQUENCE(;COUNT(x);2));999)-x))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-12-2021
    Location
    Europe
    MS-Off Ver
    365
    Posts
    45

    Re: Split cell value based on number, after every number appereance in cell

    It works, this is amazing !
    Thank you very much

+ 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] Create number list for a second cell based on number selected in first cell
    By AceCo55 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-31-2018, 10:40 AM
  2. Replies: 2
    Last Post: 08-12-2015, 01:01 AM
  3. Replies: 3
    Last Post: 07-23-2013, 07:20 PM
  4. Replies: 1
    Last Post: 07-18-2013, 10:11 AM
  5. [SOLVED] Excel VBA/Macro - Copy Number of Rows based on the number in a cell to another sheet
    By diane.alexander58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2012, 11:32 AM
  6. Split cell before number?
    By majahops in forum Excel General
    Replies: 2
    Last Post: 02-09-2008, 08:08 PM
  7. [SOLVED] formatting cell number based on previous cell number
    By Pasquini in forum Excel General
    Replies: 3
    Last Post: 06-20-2006, 01:40 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