Search a cell that contains words and numbers and convert the numbers to metric

Carchee · #31 10-06-2014, 10:01 AM

I am integrating the code into our spreadsheet which for some reason gives me errors (discussed in post #20). I can create a new sheet within the spreadsheet and have the program run there without any error, however it would be nice use it on a current tab and have the code work. I would like to try one last thing before resulting to creating a new sheet.

As per your quote in post #23

Quote:

What is does is to take a string that might be expressed as, say:
1' 3"
and replace the ' with *12+0, so you end up with:
1*12+03
for a feet_&_inches:inches conversion (in this case, 15in), so the in:mm conversion can be done.

Because all dimension are given in inches (besides the pump feet which is already converted) there is no need for the "*12+0" code. So maybe if I alter the line of code to only handle the inches (IN and ") then maybe that will clear the error.

I have tried to alter the line of code but I'm wasn't fully confident in what I was doing with that line, plus what I was doing wasn't working.

I realize you don't get the error as we have discussed earlier but just maybe if we eliminate any code that pertains to a feet and inch dimension (ex. 2'-4" or 1'3") which doesn't exist in the list then maybe the error will go away. It is my last effort and then I will accept that I need to add it to a new sheet. Thanks for hanging in there with me and for all your help Paul.

macropod · #32 10-06-2014, 08:31 PM

Since it seems you don't need the 'ft & in to in' conversion, you may as well comment-out or do away with the three lines containing:
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))

Carchee · #33 10-07-2014, 08:30 AM

The first part of comment #27 and #26 talks about this and that I tried commenting those out and it did fix it so I wasn't getting any errors, however you'll see in those posts that when I comment them out the code doesn't convert some of the " units. So I'll have to modify them somehow so it'll still convert all the " and IN units but not look for the feet units.

macropod · #34 10-07-2014, 01:49 PM

In that case, I have to assume whatever else you have is tripping up over the Evaluate finction. Try replacing each instance of:
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
with:

Code:

              If InStr(StrConv, "-") > 0 Then
                If InStr(StrConv, "/") > 0 Then
                  StrConv = Split(StrConv, "-")(0) + Split(Split(StrConv, "-")(1), "/")(0) / Split(Split(StrConv, "-")(1), "/")(1)
                Else
                  StrConv = Split(StrConv, "-")(0) + CSng(Split(StrConv, "-")(1))
                End If
              End If

Carchee · #35 10-08-2014, 10:43 AM

Nice, I had to alter the code a bit to get it to work. This is what I did:

Code:

  If InStr(StrConv, "-") > 0 Then
                StrConv = Split(StrConv, "-")(0) + Split(Split(StrConv, "-")(1), "/")(0) / Split(Split(StrConv, "-")(1), "/")(1)
                Else
               If InStr(StrConv, "/") > 0 Then
                  StrConv = Split(StrConv, "/")(0) / CSng(Split(StrConv, "/")(1))
                End If
              End If

It all works nicely and I don't get any errors and I don't have to create another sheet to get the job done.

If I decide to use the superscript Sub how would I go through and delete the carrot '^' after it is superscripted so just the number is left? I tried using a replace which did delete the '^' symbol but it was un-superscripting the numbers. I used the following code:

Code:

With .Cells(i, 3)
    Columns("C").Replace what:="^", replacement:=""
    End With

After the 'Next' and before the 'End With' 'Application.ScreenUpdating = True' at the end of the whole Sub

macropod · #36 10-08-2014, 01:42 PM

To omit the ^ symbols, you could change:

Code:

        .Characters(Start:=j, Length:=k).Font.Superscript = True
        j = j + k

to:

Code:

        .Characters(Start:=j, Length:=k).Font.Superscript = True
        .Characters(j, 1).Delete
        j = j + k - 1

Carchee · #37 10-08-2014, 03:16 PM

Thanks, I knew it would be something simpler than what I was thinking. It all works great! Thank you for your help Paul, I really appreciate it. I can now say that it does everything I want it to.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Words in my document were converted to numbers????	MikeD23	Word	1	08-26-2012 11:09 AM
can word convert numbers?	koolfire	Word	2	01-07-2012 02:12 PM
Convert numbers to a specific text string	francis	Excel	1	10-06-2011 01:43 PM
Numbers in Words	janak	Excel	2	12-19-2010 08:53 PM
Numbers Convert in word in MS Access	towhid	Office	0	08-19-2010 01:17 AM