Search and Replace conundrum 3

[MattGreer]

So the data I'm working with has a column with lengths of pipe written like this: 5'4"

The procedure I copied off the internet (quite thorough and lengthy, let me know if you want the link) will parse that into decimal feet, but it requires a separator between feet and inches. So I do a search/replace as such:

    With Cells(1, iQty).EntireColumn
        .Replace What:="'", Replacement:="'-"
    End With

(btw anyone reading anything I write, please feel free to be very judgmental and tell me better ways of writing things. Always appreciated!)

Problem is, if I re-run the code, now I get double dashes and that blows up the function that calculates feet. I suppose I could just go back in and search for and replace double dashes with a single dash each time, but, is there a better way? Only thing I can think of is to go through each cell in the column and use InStr to search for a dash and if there isn't one there, to skip to the next cell. Seems like a very time-consuming thing to do.

Thoughts? Advice? I'd rather not modify the decimal-feet calculation function but perhaps that might be the best place to handle this sort of thing, long term.



Thanks!!


Matt

 

[SkipVought]

Matt

You have dimensions as x'y", yes?

Dim a as variant, iFt as integer, iIn as integer

a = Split(YourDimension, "'")
If Ubound(a) = 1 Then
   iFt = a(0)
   iIn = a (1)
   iIn = Left(iIn, Len(iIn)-1)
Else
'Error not feet and inches.
End If

That's assuming that there are no fractional inches.

I'd do the inches to feet right here, one more line to get x'y" as x.z feet, but change iFt as integer to nFt as single or double

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

It would be helpful (to me, at least) if you would also say:

This is what I have (with some representative data):[tt]
2'4"
45'7"
2'4.564"
etc.[/tt]

And I would like to convert it to:[tt]
???
???
???[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

Seems silly to modify your data to be able to accommodate a function to modify your data.

As Skip suggests, just do it all in one shot.

Either change the function you found to work without the "-", or add the additional 3 or 4 lines needed to Skip's code to get you there.

 

[strongm]

> it requires a separator between feet and inches

But you've already got one! The '

Might actually be easier to rewrite the decimalisation function than doing global search and replacement

>quite thorough and lengthy
Erm ... really? Should be quite simple to do this in Excel ... let's see ... how about:

[blue]Public Function DecimalFeet(strMeasure As String) As Single
    Dim feetinches As Variant
    If InStr(strMeasure, "'") = 0 And InStr(strMeasure, Chr(34)) <> 0 Then strMeasure = "0'" & strMeasure
    strMeasure = Replace(strMeasure, Chr(34), "") [COLOR=green]' strip inches symbol; we don't need it[/color]
    feetinches = Split(strMeasure, "'")
    DecimalFeet = feetinches(0)
    If UBound(feetinches) = 1 Then
        If feetinches(1) <> "" Then DecimalFeet = DecimalFeet + Evaluate(feetinches(1)) / 12
    End If
End Function[/blue]

Think this should handle most legit variants of input.

Note that I said 'In Excel'; this won't work as written in other VBA hosts, as Evaluate is actually an Excel function, not a VBA function.

 

[Andrzejek]

And, again, you can use strongm's Function in your VBA code or as a formula in your cell(s)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

I would be inclined to pass the input string through TRIM() and Application.Clean() in strongm's code.

Just to remove any spaces or other junk that might be in the string.

 

[MattGreer]

ROFL, strongm, you are amazing.

But, this is where I got the code. Has a lot of error checking and all sorts of other stuff going on, and I'm up to my eyeballs in regex (it's loads of fun tho ngl) that I just left it as is. Here's a link:

https://engineerstoolkit.wordpress.com/2017/03/05/dealing-with-feet-and-inches-in-excel/

I think that code allows for fractional inches. While I don't have that at the moment, I'm sure I will someday in which case it'll be handy to have.

All y'all are hilarious, and amazing. Thank you! Stars for all!

Thanks!!


Matt

 

[strongm]

>I think that code allows for fractional inches

So does mine. Try it (actually there was a minor bug in my code, caused by me being too lax in testing and temporarily forgetting that VBA doesn't shortcircuit multistatement IF statements when shortening my original code; code above has now been edited to fix that)...

[blue]Public Sub Example()
    Debug.Print DecimalFeet("4' 1 3/4" & Chr(34))
    Debug.Print DecimalFeet(" 4' 1 3/4")
    Debug.Print DecimalFeet("4' 1.75" & Chr(34))
    Debug.Print DecimalFeet("4'  1 3/4")
    Debug.Print DecimalFeet("4'1 12/6")
    Debug.Print DecimalFeet("4'")
    Debug.Print DecimalFeet("4 '")
    Debug.Print DecimalFeet("4")
    Debug.Print DecimalFeet("3/4" + Chr(34))
End Sub[/blue]

>I would be inclined to pass the input string through TRIM() and Application.Clean()

Shouldn't need trim. Evaluate and the internal type conversion from string to numeric apply a trim already.

 

[Andrzejek]

You forgot this example:[tt][blue]
Debug.Print DecimalFeet("5' 13" & Chr(34))[/blue][/tt]
Some people like to be 5'13", but your code takes care of that, too


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Dang it strongm I should just quit doing all this searching on the Internet and just email you directly.

Did you look at that guy's code? Is it just complete and total overkill?? Redundant?

Thanks!!


Matt

 

[mintjulep]

That Engineerstoolbox code allows you to set the separate between ft and inches.

I haven't tried to fully understand it yet, but it does seem seem to deal with a lot of potential things that might be fed to it.