Separate values

that would be too easy:)

This is what cell A1 looks like:

31622,31623,316
24,31625,31628,3
1629

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.

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:= _
        True
End Sub

Select allOpen in new window

Saurabh...

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.

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...

Saurabh...

It did not work for me...I am attaching a file for you to test it...Thank you!
Macros.xlsx

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...

so nothing can be done except going line by line and separate them?

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...

Saurabh...
Macros.xlsx

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.
Macros_dgb.xlsx

Doug,

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..

Saurabh...

Thank you for all your efforts!

Saurabh,
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?