Microsoft Excel - Separate number in a single cell

Asked By Dan on 30-Dec-13 05:37 PM

I am trying to figure out how to separate five number in a single cell. So, in column B1 to B1000 there are numbers in each cell. They are separated by a space and not a comma. So, for example, in A1 there are five numbers that need to separated (01 02 03 04 05). Each number needs to be put in a cell starting at E1 and end at I1. Example is: E1=01, E2=02 and so on.

Harry Boughen replied to Dan on 31-Dec-13 04:05 AM

Hello Dan,
E2: =LEFT($A2,SEARCH(" ",$A2,1))
F2: =MID($A2,LEN($E2)+1,SEARCH(" ",$A2,LEN($E2)+1)-SEARCH(" ",$A2,1))
G2: =MID($A2,LEN($E2&$F2)+1,SEARCH(" ",$A2,LEN($E2&$F2)+1)-SEARCH(" ",$A2,LEN($E2)+1))
H2: =MID($A2,LEN($E2&$F2&$G2)+1,SEARCH(" ",$A2,LEN($E2&$F2&$G2)+1)-SEARCH(" ",$A2,LEN($E2&$F2)+1))
I2: =RIGHT(A2,LEN(A2)-LEN($E2&$F2&$G2&$H2))

This doesn't matter whether you number groups are more or less than two characters.
Regards and Happy New Year,
Harry

Dan replied to Harry Boughen on 31-Dec-13 07:22 AM

Thank you. That worked perfectly.

Dan replied to Harry Boughen on 31-Dec-13 08:32 AM

I now have question? once I put the formulas in to the cells they return the correct number. Now when I try to use the mode function to find the most frequent number from a column I am getting an error? The error only appears when there is a formula in the column? I do not understand how to over come this.

Harry Boughen replied to Dan on 31-Dec-13 02:58 PM

Hi Dan,
That will be because what you are seeing are strings.
You will have to enclose the formulae inside the VALUE() function eg = VALUE(LEFT(etc)) etc
Regards
Harry

Sorry Dan - that will break all of your formulae will have to think of something else :(

Harry Boughen replied to Dan on 31-Dec-13 03:29 PM

Hello Dan,
This will give you the numbers.
E2: =VALUE(LEFT($A2,FIND(CHAR(7),SUBSTITUTE($A2," ",CHAR(7),COLUMN()-4))))
F2: =VALUE(MID($A2,FIND(CHAR(7),SUBSTITUTE($A2," ",CHAR(7),COLUMN()-5))+1,FIND(CHAR(7),SUBSTITUTE($A2," ",CHAR(7),COLUMN()-4))-FIND(CHAR(7),SUBSTITUTE($A2," ",CHAR(7),COLUMN()-5))))

The formula from F2 can be copied across. If you use columns other than E to I you will have to change the integers 4 and 5 in the formula to suit.

Regards
Harry

Dan replied to Harry Boughen on 31-Dec-13 03:40 PM

The five combined numbers are in B1 and I am using F thru J when they are split. I am confused, I changed the 4 to a 5 and it returned the first number correctly but the rest are the same as the first?

Dan replied to Harry Boughen on 31-Dec-13 04:17 PM

I figured it out. Thank you and that works.