CODEWITHSUNDEEP
databaseexcelexcel-2010
Maciej Wojcik
Maciej Wojcik

Reputation: 2171

How to separate data in excel

i have big problem with my last excel. I have many cells which looks likt this "text text text number, number, number" I wish to have one cell with" text text text" and the other one with "number, number, number" The problem is that in different cells ther are different number of "texts" or "numbers".

So i think, that i have to make formula which find first "," then go left for first " " and then separate form this place.

Please, can someone help me with this formula?

Upvotes: 3

Views: 143

Answers (1)

Mark Balhoff
Mark Balhoff

Reputation: 2356

Implementing the logic you suggested is probably simplest in the following form. Assuming your data is in cell A1, place in cell C1:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(",",A1))," ",REPT(" ",20)),20))&RIGHT(A1,LEN(A1)-FIND(",",A1))

Place 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 taking 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 grab the last 20 characters and trim off the remaining spaces. What we are left with is the string "number," (first number). Thus we don't care exactly how many characters that number is. Then we append 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 number substring to get the sequence of text strings like so =LEFT(A1,LEN(A1)-LEN(C1)-1).

Upvotes: 2

Related Questions