# formula for splitting between cell value

1. ## 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  Register To Reply

2. ## Re: formula for splitting between cell value  Register To Reply

3. ## 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.  Register To Reply

4. ## 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.``  Register To Reply

5. ## 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!  Register To Reply

6. ## Re: formula for splitting between cell value

Hi alan and john thank you.
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.  Register To Reply