+ Reply to Thread
Results 1 to 6 of 6

formula for splitting between cell value

  1. #1
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    427

    formula for splitting between cell value

    Hi,
    I want help in the following.
    I have A1 - 1 22 3 45 5
    Like above 5 digit numbers with space between then.
    So totally 4 space.
    i want the sum of the numbers .
    ie for above - 1+22+3+45+5. i have more rows -1l more. so vba is not working properly. i want a formula to extract and sum.

    and in the same way. the above to extract and group
    b1- 1a&b 2e&h 33a&b 4e&a 55b&h 62e&h

    i want to extract a&b e&h e&a b&h numbers separately into a cell grouped.
    Extraction to column c,d,e,and so on from b1 by formula
    c1 to be 1a&b 33a&b
    d1 to be 2e&h 62e&h
    e1 to be 4e&a
    f1 to be 55b&h

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,488

    Re: formula for splitting between cell value

    Please read the yellow banner at the top of this page on how to attach a file.

  3. #3
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    427

    Re: formula for splitting between cell value

    Hi Alan,
    Attached the file.
    Input and output needed are mentioned.
    Can anyone provide me the formula. I used left mid right. but some are not coming across correct
    and i need sum as well as extract.
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,488

    Re: formula for splitting between cell value

    Can be achieved using Power Query. See my signature to learn more

    Please Login or Register  to view this content.
    and second scenario
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    287

    Re: formula for splitting between cell value

    Check this option with formulas:

    1.)
    PHP Code: 
    =SUM(IFERROR(--MID(SUBSTITUTE(" "&A2," ",REPT(" ",50)),50*ROW($1:$20),50),)) 
    2.)
    PHP Code: 
    =TEXTJOIN(" ",,IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(" "&$D2,E$1,"")," ",REPT(" ",99)),99*ROW($1:$20),99)&E$1,"")) 
    Blessings!
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    427

    Re: formula for splitting between cell value

    Hi alan and john thank you.
    Alan thanks saw the link..
    john one doubt row function rept function 50 and 99 what is the idea?
    can you explain the formula.it would be helpful for me.
    and is there any alternative for textjoin for older excel version. for me at home it is working 365 at office excel 10 .. so only asking.

+ 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. Splitting data from one cell to 2 cells by formula
    By Derek B in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-08-2015, 06:12 AM
  2. Formula for splitting cell contents into two rows?
    By sccrbrg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-28-2014, 05:56 PM
  3. [SOLVED] Splitting formula Values from one cell into separate cells
    By thegrandmaster in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2013, 02:17 AM
  4. is splitting a formula possible in VBA?
    By GreenTee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2013, 03:09 PM
  5. Cell Splitting Formula
    By Pauled in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-13-2010, 05:54 PM
  6. Replies: 7
    Last Post: 02-14-2009, 11:35 AM
  7. Splitting Cell Data + Formula
    By duross in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-09-2008, 03:23 PM

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