Search code examples
excelreplacecell

How to add spaces before and double number and change units of measure Excel

I am trying to organize a database that has no proper convention. I have the below code that does some of the jobs but I need to add space between a double number and MM and also converting 1/2, 1/4, 3/4 to .5, .25 and .75.

This is what I have so far. I can use find and replace but I have hundreds of tabs and thousands of products

=IF(ISERROR(FIND(UPPER(D$2&" "),UPPER(INDIRECT(D$1&ROW())&" "))),TRIM(UPPER(INDIRECT(D$1&ROW()))),TRIM(SUBSTITUTE(UPPER(INDIRECT(D$1&ROW())&" "),UPPER(D$2&" "),UPPER(D$3&" "))))

I tried 

=TRIM(REPLACE(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&"1234567890")),0," "))

But this doesn't seem to work and couldn't plug it into my original formula

=IF(ISERROR(FIND(UPPER(D$2&" "),UPPER(INDIRECT(D$1&ROW())&" "))),TRIM(UPPER(INDIRECT(D$1&ROW()))),TRIM(SUBSTITUTE(UPPER(INDIRECT(D$1&ROW())&" "),UPPER(D$2&" "),UPPER(D$3&" "))))

So I need to change this

( 100mm screw 1/4 inch pipe )

To

(100 mm screw .25 inch pipe)

Solution

  • Here is a partial answer.

    The following User Defined Function will place a space between a number and label:

    Public Function SpaceMaker(sIn As String) As String
    Dim temp As String, i As Long, L As Long, nxt As String
    temp = Left(sIn, 1)
    L = Len(sIn)
    For i = 2 To L
        nxt = Mid(sIn, i, 1)
        If Right(temp, 1) Like "[0-9]" And Not nxt Like "[0-9]" Then
            temp = temp & " " & nxt
        Else
            temp = temp & nxt
        End If
    Next i
    SpaceMaker = temp
End Function

    For example:

    enter image description here

    It does not convert fractions into decimals.