Massive Cloud Service Migration, Need help building a Excel template...

**vendettadead** · 04-18-2013, 01:40 PM

Greetings,

My name is Paul,

I have been tasked with migrating millions of documents up to a cloud service. I need to submit an index .csv that will allow me to push data up without the need to manually profile the data.

Here is a sample of the document I am creating:
A B C D E F G H I J K L
filepath DOCUMENT NAME NDDOCID NDDOCIDDATE CREATED BY CREATED DATE LAST MODIFIED DATE Notes Author Matter DocType DocSubType

I have a program called DirTrace that will populate the “filepath” A column field. From this I am trying to extract the information I need.

It will produce something that looks like this:
A
filepath
F:\DAN\03000017\Agreements\
F:\DAN\03000017\Correspondence\
F:\DAN\03000026\Real Estate\
F:\DAN\03000029\Entities\

We have spent the last few months organizing the entirety of our network data into this format:

\Attorney Name\Matter Number\Document Name
or
\Attorney Name\Matter Number\Document Type\Document Name
or
\Attorney Name\Matter Number\Document Type\Document Subtype\Document Name

I would like to take the information found in Column A to populate information out into some of the other fields

Column B needs to extract only the Document Name information from Column A.
Example:

So we would ignore everything but Reagan Perpetual Easement (10-22-12)-4851-1212-4433 ver. 1.docx

Column K needs to extract only the DocType information from Column A.
Example:

F:\DAN\03000017\Correspondence\
So we would ignore everything but Correspondence

Column L needs to extract only the DocSubType information from Column A.
Example:
F:\DAN\03000017\Correspondence\Letterstomom\
So we would ignore everything but Letterstomom

And so on.

In any case I hope I have been clear about what I am driving at.

**dynamoracing** · 04-18-2013, 02:22 PM

Found this here: http://stackoverflow.com/questions/2...ons-in-a-field

If you can use intermediate formulae, then this will work:

A1 -- save;the;national;treasure;for;good

B1 -- blank

C1 -- =IFERROR(FIND(";",$A1,1+(B1)),LEN($A1)+1)

copy C1 into D1:H1

C2 -- =MID($A1,B1+1,(C1-B1)-1)

copy C2 into D2:H2

Row 1 will display the position of each semi-colon in A1, because it starts looking in the string one character past the semi-colon found in the previous cell.

eg cell E1 searches for a semi-colon in A1 starting at D1+1 =10.

The iferror statement in C1:H1 traps the error which will occur when the search finds no further semi-colons, and returns the full length of string A1, plus 1 for an imaginary semi-colon at the end.

B1 needs to be blank to create an initial zero.

Cells C2:H2 then use the Mid function to copy the section of the A1 string starting one character after the value in each of B1:G1, with length (C1-B1)-1, (d1-c1)-1 etc (minus one to cut out the semi-colon itself)

You should get: 5, 9,18,27,31,36 in Row 1, and beneath those cells the individual words.

Hope this helps.

Works For me

Here is an example

Example_Cloud.xlsx

or you can do this:

You can create a VBA function to split the fields from this example:

Function ExtractElement(str, n, sepChar)
' Returns the nth element from a string,
' using a specified separator character
Dim x As Variant
x = Split(str, sepChar)
If n > 0 And n - 1 <= UBound(x) Then
ExtractElement = x(n - 1)
Else
ExtractElement = ""
End If
End Function

Then the A2 formula would be: =ExtractElement(A1, 1, "\") and A3 would be: =ExtractElement(A1, 2, "\") and so on

Here is an example of second solution:

Example_Cloud2.xlsm