Extract from Filename - Excel

[rnordquest]

I need a macro to extract a number from the filename to use in it's calculation. Some measuring machine is programmed to return x-y coordinates at a constant cutting plane z with the z given in the filename. I need to transform the coordinates and output x-y-z. Here are examples of the filenames.

Y1.5000-Pre Main-1.txt
Z-2.7000-Pre Main-1.txt

Thanks,

Roger

 

[ColourfulFigsnDiags]

Can you get the file name into a variable in your macro? If so it will be a fairly simple matter to use a combination of the string manipulation functions to extract the number ...

Read the Eng-Tips Site Policies at FAQ731-376

 

[rtmote]

How well do you know macros?
Method 1 if you are not strong:
Write into a cell the sheetname.
Then use text to columns to strip out the common elements using fixed widths. This will delete X and Y
Then repeat with text to columns using delimited width where it will look for -P and split again
Hide the columns with the redundant components.
Method 2:
Usig right(), left() or trim() in the macro on sheetname.

I will upload examples later if you want.

 

[rnordquest]

You gave me the key. What I was doing wrong was setting the filename into a variable. I couldn't get the string functions to work with it in that form. I'll put it in a cell and use an If Then Else to decide what to do to strip away the excess using the Mid function. I can do one thing where the string starts with Z and the Else for everything else.

Thanks

 

[ColourfulFigsnDiags]

Ok easy done ...

If file name is in cell A3
=LEFT(RIGHT(A3,LEN(A3)-1),FIND("-Pre",RIGHT(A3,LEN(A3)-1))-1)

Will give you the number

Read the Eng-Tips Site Policies at FAQ731-376

 

[ColourfulFigsnDiags]

And yes I know I could have done that more cleanly with a MID function but that is not the way my brain works LOL

Read the Eng-Tips Site Policies at FAQ731-376

 

[rnordquest]

I tried the =LEFT and got an error. It says the Sub or Function is not defined and it hilites "FIND".

 

[ColourfulFigsnDiags]

Grrr what version of Excel are your using?

Try replacing "FIND" with "SEARCH" in that formula.

Read the Eng-Tips Site Policies at FAQ731-376

 

[rnordquest]

Same problem. I tried surrounding it using a With and .FIND but got another error. Invalid procedure call or argument. Here is what I'm running. My If statement sort of returns the numbers I want but the check always goes to ELSE so it only works half the time.

tf = ActiveWorkbook.Path & "\ImpellerFiles\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(tf)
Set fc = f.Files
iFile = 1
cIndex = 6
For Each f1 In fc
Workbooks.OpenText Filename:=tf & f1.Name _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma _
:=False, Space:=True, Other:=False, OtherChar:="|", FieldInfo:=Array( _
Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True

BladeWorkbook = ActiveWorkbook.Name
Range("k1").Activate
ActiveCell.Value = BladeWorkbook

With Worksheets(1).Range("k1")
Cut = Left(Right(k1, Len(k1) - 1), .Find("-Pre", Right(k1, Len(k1) - 1)) - 1)
End With


If (ActiveCell.Value <= Z) Then
Z = Mid(BladeWorkbook, 3, 5)
Else
Y = Mid(BladeWorkbook, 2, 5)
End If

 

[ColourfulFigsnDiags]

As I said, try replacing the FIND function with the SEARCH function, Their input syntax is identical and they are almost identical in function (one is case sensetive (FIND) and the other isnt(SEARCH)), that should work.

Read the Eng-Tips Site Policies at FAQ731-376

 

[rnordquest]

I did that and addressed it with my 1st line to which "Same problem." It says the Sub or Function is not defined and it hilites "FIND" or "SEARCH".

 

[ColourfulFigsnDiags]

Oh sorry, I thought you were putting this in a spreadsheet cell, NOT in a MACRO?!

In a MACRO, assuming all the files have "-Pre Main-1.txt" as the final text (or at least the same number of characters) Then it is simply

MID(k1,2,LEN(k1)-16)

... assuming the last bit is always the same.

Read the Eng-Tips Site Policies at FAQ731-376

 

[rnordquest]

Yep, a macro. I have the same number of ending characters but I don't have the same number of beginning characters so it has to be

MID(k1,2,LEN(k1)-16) and the other times

MID(k1,3,LEN(k1)-16)

I have yet to make it descern betweent the two. See my If statement at the end of the macro. It works to extract the number in the filename in only one of the cases. I've got a niggly "-" before the number in one type of file.

Y1.5000-Pre Main-1.txt
Z-2.7000-Pre Main-1.txt

 

[ColourfulFigsnDiags]

I thought you would want to keep the -ve sign as it it clearly a position?

Still... no problem, just makle it ABS(MID(k1,2,LEN(k1)-16)) ... that should strip the -ve sign ... I am pretty sure that ABS is still a macro function if not, just square and then squareroot the result.

Read the Eng-Tips Site Policies at FAQ731-376

 

[rnordquest]

I wouldn't have thought ABS would work on a string. I'll give it a try. I have another idea too. Instead of coming in from the left or mid, I should grab my stuff by coming from the right side of the filename since all the files have the same length after that "-".

 

[brengine]

rnordquest,

If you don't need to open the file then I wouldn't even do that. I did the same thing in your For loop without having to open the file and use the cell K1 for swaping values. I did (and recommend) that you just stick with manipulating varibles in VB and only grab cells for input or final output. Trying to go back and forth between them seems too confusing. If you do need to open the file, the below code will still work.

This is assuming these are actually filenames:
Y1.5000-Pre Main-1.txt
Z-2.7000-Pre Main-1.txt

Work for you?
Ken

    tf = ActiveWorkbook.Path & "\ImpellerFiles\"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(tf)
    Set fc = f.Files
    iFile = 1
    cIndex = 6
    For Each f1 In fc
        
        BladeWorkbook = f1.Name
        
        Axis = Left$(BladeWorkbook, 1)
        Value = Mid$(BladeWorkbook, 2, InStr(BladeWorkbook, "-Pre") - 2)
        If (Left$(Value, 1) = "-") Then
            AbsValue = Right$(Value, (Len(Value) - 1))
        Else
            AbsValue = Value
        End If
        
    Next

 

[rnordquest]

Yes those are shortened forms of the filenames. They use some machine to measure the surface contour of the impeller in a plane (constant y or z) with the cutting plane's dimension in the filename. So I have to open the file to replace the bogus y or z with the real one then transform to a different coordinate system for output. I also have to string several files together to get the complete surface. There are 3 files to describe each cut.

Thanks so much for your help,

Roger