Separate long data that is in a single cell into several cells

[acsishere]

Dear Friends,

I have some thousands of data that require split based on space between two values and each value to be copied in a different cell.

I have explained the requirement with a sample data, which may give clarity.

Request your kind help.

[JasperD]

Hi there,

I think the following should give you some basis to work on. Since I don't know which range your data is in and where you want the output to be posted, my code looks at your cell A3 and puts the output in row 1. Of course, you can ammend this easily in the code.

Also, right now, I didn't look into getting the name into one cell instead of seperated cells by each space. If I have time, I will look into it later - but I think you should be able to solve this on your own from here.

Please Login or Register  to view this content.

Please click on the * below if this helps

[acsishere]

Dear Jasper, Thanks for your understanding &code. You cracked it and it works as required.

However, when tried with the data, it converts only the first row. I need that:

1) The entire (sample) rows (but my range is having >1000 rows) to be split into each cell and copied into another sheet.

2) When executed your code, the last character of cell A3, i.e. "A" is not appearing in the last cell. That is to be taken care.

I attached the file with your code for your instant ref.

Request your kind support.

[JasperD]

Hi acsishere,

your uploaded sheet was 100% same as the previous sheet you had attached, so that wasn't much help.
Anyway, try the code below.

Please Login or Register  to view this content.

Please remember to click on the * below if this helps

[jindon]

No macro

1) Select Col.A
2) Data - Text to Columns - Delimited - Sapce
3) Select the field info as Text for any column begin with 0

Or macro

Please Login or Register  to view this content.