Data to Column, remove unwanted fields

[skyh3ck]

Hello

I have long list of data saved in word, i want to copy it to excel and with the help of Macro, i want to convert it data to column, but remove some duplicate fields and have same data in one column.

i have created a macro with steps records, however some of the fields have multiple entries and due to which the row has additional fields, now i want to remove those additional fields, so that i have one data type in one column only, please see the attached word and excel file for example

below is the code from VBA editor

>>> use code - tags <<<

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=True, Comma:=True, Space:=False, Other:=True, OtherChar:= _
        ":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
        Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1) _
        , Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array( _
        19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), _
        Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array( _
        32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), _
        Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1)), _
        TrailingMinusNumbers:=True
    Range("A1").Select
End Sub

now i want to remove the additional name, or do something that same type data is in same column

 

[Marc L]

Hello,​

​

two issues with your initial post :​

​

1) Use code tags or the Code option within the 3 dots icons.​

​

2) This is a wild cross posting !​

You can do cross posting but you must share a link for each other thread created on other forums …​

​

 

[skyh3ck]

i dont know what is wild cross posting

anyway

i have another post with same problem, at

Data to Column, remove unwanted fields

Hello I have long list of data saved in word, i want to copy it to excel and with the help of Macro, i want to convert it data to column, but remove some duplicate fields and have same data in one column. i have created a macro with steps records, however some of the fields have multiple...

www.excelforum.com

also example file added on top

 

[Marc L]

As the source Word document was made by foot - inconsistent - so the TextToColumns seems not the best way to go.​

According to this source Word document attach here at least the exact expected final result Excel workbook​

in order some helper can give it a try, reproducing the same layout …​

Indicate also your Office version and your OS version.​

​

 

[Marc L]

As it seems you already have some way in another forum​

but it can be achieved directly from the Word document rather than copying manually the data to an Excel worksheet …​

​

 

[p45cal]

I can get your data like in the attached Excel workbook.
All headers that might exist are included, even if only present in one record (see ALIAS 1 header). Dates are real Excel dates.
The problem is it's difficult to do this directly from Word (I pasted into a .txt file first); surely the data can't come to you in Word format? It must be in another format before that. It's VERY likely that you can do an import of information more robustly from the data prior to it being in Word.

I'm guessing now: your data look like they may have come from files such as .ini files which sometimes look like this:


where each field is on a separate line.
If your data come from files even a bit like this, it's easier to leave them as they are, even if there are lots of files.

 

[Marc L]

it's difficult to do this directly from Word

No that's easy to read the Word document under Excel VBA just with few basics codelines …​

The difficulty here belongs to the initial post where important informations are missing like the exact expected result workbook as well !​

​

 

[skyh3ck]

I can get your data like in the attached Excel workbook.
All headers that might exist are included, even if only present in one record (see ALIAS 1 header). Dates are real Excel dates.
The problem is it's difficult to do this directly from Word (I pasted into a .txt file first); surely the data can't come to you in Word format? It must be in another format before that. It's VERY likely that you can do an import of information more robustly from the data prior to it being in Word.

I'm guessing now: your data look like they may have come from files such as .ini files which sometimes look like this:
View attachment 78536

where each field is on a separate line.
If your data come from files even a bit like this, it's easier to leave them as they are, even if there are lots of files.

Hey Thanks, i checked your file and that is how i want my table.

so the data i get is from an in house application where i can select all data and paste it in excel, word, note pad etc.

there are hundreds of records to deal with and each records has same header, like Name, NTLT, Book, category etc. so to remove that i use tect to column,

the problem i face is some of the records have Alias, and some has Alias 1, so i have to manually delete those data and move cell to left to align with proper column

from the word file or note pad how can i do so that i get all data sorted in column

like

can we do it with macro, so that those who are note familier with excel can just run macro and data is sorted

how you do it in table, please help

 

[skyh3ck]

ok, so i have data in either in word or text

now each records alread has header like Name, Alias, NTLT, followed by ":" how can i sort as per the header

the only problem i face with text to column is thas some of record has Alias, Alias 1, and two city of birth separated by comma.

please guide me how can i do it

 

[skyh3ck]

As it seems you already have some way in another forum​

but it can be achieved directly from the Word document rather than copying manually the data to an Excel worksheet …​

​

how, please help, i mean just how can arrange it in a table with headers which are alreay present in each record

 

[p45cal]

Easier to put it into notepad and save as .txt.
Could you do that and post it here? Don't just copy from Word (Word mangles things), get the data as directly as you can from your in-house app.
I'm using Power Query to transform the data, all that will be needed is to refresh the table (or get a macro to do it) after the .txt file has been updated.
A definitive list of the column headers you want to keep would be useful.
At the moment I'm using the presence of a colon in a record to determine the end of the column header, then I'm looking for the comma directly before each colon to determine the start of a header name, but this relies on there being:

• always a colon at the end of a header
• never a colon in the data itself
• there always being a comma in a field even if there is no data

I feel those criteria could easily 'fall over' at some point so I'm looking for a more robust way, hopefully getting the data in a different way (maybe there are tabs in the data (that Word is losing)) that would help. Usually an application can export data in a variety of ways - have you explored different ways that we could use as a starting point (instead of copy/paste).
What is the in-house app?
What version of Excel are you using?

 

[Marc L]

how, please help, i mean just how can arrange it in a table with headers which are alreay present in each record

That's easy from the Word document as it is but as we are not on a mind readers forum (which table ?!)​

so just do the necessary like any Excel forum expects in the initial post as yet asked in posts #4 & 7 :​

The difficulty here belongs to the initial post where important informations are missing like the exact expected result workbook as well !

So, for the third time, according to your attachment Word document​

attach at least the exact expected result Excel workbook with your best elaboration !​

​

Without I could post an one shot VBA demonstration importing all the data from the Word document like the post #6 result workbook​

then you will have to amend it yourself if you do not need all the data …​

​

As a reminder solution belongs to good enough readers …​

​

 

[skyh3ck]

Sir, i repect and appreciate your help

the data is sent by our vendor/client in word, notepad format, we can not control that, so we manually paste it in excel and arrange in a table

please see attached, even if i get all headers as table header and all data in column that will be fine,

please see the attached excel for what i want

 

[Marc L]

Are you sure ? As this is the same workbook than in post #6 !​

So different than this thread title and what you asked in the other forum …​

​

 

[p45cal]

In the attached:
Cell B2 in sheet Control needs to be updated with the full path and name to your text file. (Cell B2 is a named range FullPath)
Then on sheet Sheet1 right-click somewhere in the table and choose Refresh.
If you get the path wrong you'll get a message like this:

 

[Marc L]

To any helper :​

​

the original poster already got an Excel VBA procedure on another forum importing directly the Word document​

even if it's slower than my usual way (half codelines shorter) but that no matters with such small document sample …​

​

 

[skyh3ck]

hello update

i had one post on excel forum, which i have requested to close or to be deleted there, so i can continue this topic here

ok all i just want is to extract last name in one column, first and middle name in one column, book number in one column, employer in one column, category class in one column,

user can just paste the data in excel from the word or text and run the macro and we get all data extracted in another sheet or same sheet that can just extract name, book number, category class etc

thats it

 

[p45cal]

See attached. Same instructions as my last message. Avoid Word and stick to .txt files.

 

[Marc L]

i just want is to extract last name in one column, first and middle name in one column, […]

If you wanna a fast direct import from the Word document so, as yet required in posts #4, 7 & 12,​

attach at least the exact expected result workbook according to your post #17 attachment …​

​

… or do you want to import all 'columns' ? (As not the same coding than for partial columns like you asked in another forum …)​

​