How to separate data in excel
I have a big problem with my last advantage. I have a lot of cells that look like this text text number text, number, number " . I want one cell with text text text and another with" number, number, number ". The problem is that in different cells there a different number of "texts" or "numbers".
So, I think I need to make a formula that finds the first "," then go left for the first ", and then separate it from that place.
Please help me with this formula?
source to share
The implementation of the logic you suggested is perhaps the simplest in the following form. Assuming your data is in cell A1, put in cell C1:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(",",A1))," ",REPT(" ",20)),20))&RIGHT(A1,LEN(A1)-FIND(",",A1))
Location in cell B1:
=LEFT(A1,LEN(A1)-LEN(C1)-1)
The first formula starts with LEFT(A1,FIND(",",A1))
by finding the first comma and entering all text to the left of (and including) that comma. Then with SUBSTITUTE(...," ",REPT(" ",20))
we replace all spaces with 20 spaces. This allows us (assuming your number is less than 19 characters) to just take the last 20 characters and trim the remaining spaces. We are left with the string "number" (the first number). Thus, it doesn't matter to us how many characters this number is. Then add the rest of the numbers with RIGHT(A1,LEN(A1)-FIND(",",A1))
. Finally, we can use the length of our original string and the length of our substring of the number to get a sequence of text strings like this =LEFT(A1,LEN(A1)-LEN(C1)-1)
.
source to share