Text to Columns Seperated by Enter

[Pauldn24]

Hello,

I'm trying to figure out how to move the data I have by using text to columns.

Data is as follows:
Blank Space
Data

I tried Alt J and Alt 0010 in the "other" box and all the data disappears. Any suggestions? Thanks in advance.

 

[Peter_SSs]

I tried Alt J... in the "other" box

Try Ctrl+J

 

[Pauldn24]

Try Ctrl+J

Thanks but that does not work.

 

[pgc01]

Hi

Should work if you composed the text in excel with the alt-enter.

Did you import the data?

For ex.: sometimes, if you import data, in some text sources the delimiter for the line is carriage return-linefeed and not just linefeed like in the excel cell.

Please check.

 

[Pauldn24]

Hi

Should work if you composed the text in excel with the alt-enter.

Did you import the data?

For ex.: sometimes, if you import data, in some text sources the delimiter for the line is carriage return-linefeed and not just linefeed like in the excel cell.

Please check.

I had a software pull this data from a website. I'm not sure about "linefeed".

 

[Pauldn24]

I'm thinking about another way around it. If I use the function =left(text,number) the first two are blank and the text starts at number 3. Any ideas to delete the first two?

 

[Joe4]

I think the best way is to determine exactly what it is you are dealing with. So, we can get the ASCII number code for the character, and then use an ASCII table to see what we are working with.

Here is how you can do that. First, determine exactly where this "character" exists in your string. So, if it is in the 8th place of the entry in cell A1, use this formula to get the ASCII code:
=MID(A1,8,1)

Once we know the code, we can look up to see what it is: (Ascii Table - ASCII character codes and html, octal, hex and decimal chart conversion)
Once we know what it is, we can address it.

 

[pgc01]

I'm thinking about another way around it. If I use the function =left(text,number) the first two are blank and the text starts at number 3. Any ideas to delete the first two?

Hi

Use Find/Replace. Replace ?? with nothing.

 

[Peter_SSs]

Hi

Use Find/Replace. Replace ?? with nothing.

Pedro, won't that replace every pair of characters, not only the first 2 characters?

.. the first two are blank and the text starts at number 3

If that is consistent, then Text to Columns (Fixed width) should suffice.
If doing it manually, do Text to Columns -> Fixed width -> Click the ruler at position 2 -> Double click any other dividers that Excel may ave automatically inserted to remove them -> Next -> The 1st column of 2 characters should be highlighted so click 'Do not import column' -> Finish

If you want a macro to do that for you, try

Sub FixEm()
  Columns("A").TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(2, 1))
End Sub

 

[pgc01]

Pedro, won't that replace every pair of characters, not only the first 2 characters?

You are right, of course. I was in a hurry and did not think. Really dumb. Thank you Peter.