importing text - how to wrap?

[kileil]

Hola,
I have a comma delimited txt file that I've exported out of a Word form. I'm able to get all the fields to import into Excel nicely enough, but having a problem with one thing...

I need excel to move to the next row down, then move over a few columns and continue writing.

Layout ideally would look something like:
"Name1" "Phone1" "Unix_path1" "User_Per1" "Group_Per1" "World_Per1" "Mgr1"
"Name1" "Phone1" "Unix_path2" "User_Per2" "Group_Per2" "World_Per2" "Mgr1"
"Name1" "Phone1" "Unix_path3" "User_Per3" "Group_Per3" "World_Per3" "Mgr1"
etc...

As you probably guessed, the Word Form has a grid right in the middle of it and the excel sheet would be more usable with this "new row" inserted rather than having everything in one, very long row.

Any ideas? Something like a carriage return once it hits "Unix_Path" and a trick to copy the preceding user name/ending Mgr?

Thanks much

 

[Norie]

What do you mean by a Word 'form'?

If you have a comma delimited file then I don't see why you would have problems importing it to Excel, and having the 'fields' in the correct place.

If it's a Word table then you could probably just simply copy and paste it onto the worksheet.

 

[kileil]

What do you mean by a Word 'form'?

If you have a comma delimited file then I don't see why you would have problems importing it to Excel, and having the 'fields' in the correct place.

If it's a Word table then you could probably just simply copy and paste it onto the worksheet.

Just giving a bit of background on where the data originally came from. But the important thing is its now in a *.txt file, with each of the original fields delimited by the usual ",".

I'm mainly looking for a way to get it to wrap/newline/carriage return to a new row in excel whenever the import hits the 2nd group of the three Permissions fields within the txt file. Not sure if I'd have to explicitly call out the possible values that field could contain, or if there's an easier way.

 

[Norie]

Sorry but you've lost me, not unusual.

You've mentioned Word, text files, CSV files, permissions etc

What do you actually have and what do you actually want to do?

 

[kileil]

Sorry but you've lost me, not unusual.

You've mentioned Word, text files, CSV files, permissions etc

What do you actually have and what do you actually want to do?

hehe, yeah its confusing me as well.

Essentially I will start with one row like this:
Name1 - ItemName1 - ItemDesc1 - ItemName2 - ItemDesc2 - Manager1

And need it to become several rows like this:
Name1 - ItemName1 - ItemDesc1 - Manager1
Name1 - ItemName2 - ItemDesc2 - Manager1
etc

And I'm trying to do this in an automated fashion, probably using VBA, as we'll be seeing a lot of these conversions.I can kinda fake this layout by editing the text file manually with extra commas/returns, but its not pretty and omits some data.

 

[kileil]

Looks like I'm just going to use formulas in individual cells on a 2nd sheet to arrange the one long row on the 1st sheet into multiple, shorter rows.

Then lock that sheet to prevent accidental changes as people should only be copy/pasting out of it.

Not elegant, but it should work ok

 

[Norie]

I'm pretty sure some code could be produced to do this but before starting on anything I think you really need to clarify a few things.

Where is the data actually stored?

Is 'Name1 - ItemName1 - ItemDesc1 - ItemName2 - ItemDesc2 - Manager1' all in one cell, or is each item in a separate cell?

Is there always only 2 items, or could there be more/less?

 

[kileil]

I'm pretty sure some code could be produced to do this but before starting on anything I think you really need to clarify a few things.

Hi again! I'm done w/ the alternative way i described above, but if you're down we can look at other options too.

Where is the data actually stored?

- Originally within individual fields of an MS Word Form. But for the point of this exercise we can just say it spans one row within Excel, as that's where it ends up after import.

Is 'Name1 - ItemName1 - ItemDesc1 - ItemName2 - ItemDesc2 - Manager1' all in one cell, or is each item in a separate cell?

- Each of those are individual cells within one row of Excel.

Is there always only 2 items, or could there be more/less?

Could be one, could be 10, could be anything in between.

 

[Norie]

I'd be happy to help but you really need to be clearer.

You've posted one line of data, which isn't much to go on.

And now you've mentioned a Word form.

Oops, my bad - you did post more data previously but it doesn't match with the latest data you've posted.

I realise the data might be sensitive, if it is can you not post some fake data that is representative of the real thing.

That would probably make it easier for us to help you.

PS There are various tools on the board to display data eg Excel Jeanie.

 

[kileil]

*edit* Can't get the html tags to do anything w/ html cut out of an exported copy of the spreadsheet. No worries, I'll just go with what i've already created.