Separate values


I have about 200 rows with multiple 5 digit numbers put together but separated by a comma.  Right now I go line by line and separate the values manually. I wonder if there is a formula that can expedite this process. Here is my example: cell A1 has 93653,93654,93656 and I will need to have 93653 in B1; 93654 in C1 and 93656 in D1.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
The quickest way that i can tell you to do select your column..then go to

Data-->Text to columns-->Delimited-->Next-->Comma-->Next-->Choose Destination

And Hit ok and you are good your data will be separated like the way you want..


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You could use the text to columns function in Excel, on the Data tab. Just make sure that the columns to the right of the data source are empty, otherwise data will b overwritten.

Text to columns graphic
LadkissonAuthor Commented:
that would be too easy:)

This is what cell A1 looks like:


it's a PDF file converted to Excel . Column A might have 3 5-digit numbers or 6 5-digit numbers and they are not lined up. so when I apply text to column it only gives me this: B1 = 31622 C1=31623 D1=316 the rest of the data does not get recognized by the function.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Saurabh Singh TeotiaCommented:
You need to select your entire column..before you apply text to columns in order for it to work for all the rows.. Here is a simple macro for the same which will do what you looking for..

Sub Sepratedata()

    Columns("A:A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
End Sub

Open in new window

LadkissonAuthor Commented:
thanks but I don't use macros and I don't know where to even start....
There may be hard returns inside the cells since this was converted from a pdf document. You could use a column to reference your data cells and get rid of nonprinting characters.

If your data are in column A, you could put the following formula in column B (assumes no headings) and copy down all rows:


Then copy everything in column B and paste special/values in place. Then you should be able to use the text to columns feature on Column B.
Saurabh Singh TeotiaCommented:
Copy this code..Press alt+f11 over your workbook..which will open the visual basic editor..

Go to Insert-->New Module..and paste the code in the new module..

Close the visual basic editor and come back to your worksheet which has this data..

Press alt+f8 and you will see a macro by the name of sepratedata.. Just run the macro and it will do what you are looking for...

LadkissonAuthor Commented:
It did not work for me...I am attaching a file for you to test it...Thank you!
Saurabh Singh TeotiaCommented:
The problem in your file is that excel is not treating these values as text..excel is treating these values as number..Now i can apply a formula and get you the values where because of space it hasn't treated it as a number..But now where the space is not their for instance row number 6 ,a6..If you notice excel is treating as a number and the comma which is you see is a separator been applied by excel as a format rather then actual comma in your data...
LadkissonAuthor Commented:
so nothing can be done except going line by line and separate them?
Saurabh Singh TeotiaCommented:
Okay here is the thing..The file that you gave to me had 40 values in it..Now out of those 40 values i can't do anything on 13 values because those are number..And you need to tell me an alternate logic or way about how you want to treat them to separate them..

For rest 27..i removed the space by using substitute and then doing paste special values in next column and then doing text to column to do what you are looking for..

Those values are identified where in Column-G you see true..those are number values...

Here is the file for your reference...

Try the attachment.

Note that column G just gets rid of any commas. Then columns H through M split the string of numbers by dividing the length of the string and capturing a substring that is 5 characters long.

Cell H1 is not necessary so you can delete the 1st row if you like. However, this cell tells you how many columns you'll need for splitting out the values. The formula is an array formula. You can edit the cell range (currently G3:G42) that is referenced but you must press CTRL+SHIFT+ENTER to enter any change to the cell.
Saurabh Singh TeotiaCommented:

I also thought about the five digit logic but if you notice row-21 & 22 the total length is 6 so their got to be some other logic as well for this..

LadkissonAuthor Commented:
Thank you for all your efforts!
Good eye. The formatting seems to be inconsistent. I wonder if leading 0's could be involved somehow. Or perhaps these data were miskeyed (extra number).

What say you, Ladkisson?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.