Formatting text

[srosetti]

I have a description field in Column N. It has text in it with some
numbers..

It might look like this.

Data in Column N Row 2
Widget 12.25 x 12.1875

I'm pretty sure excel sees this whole field as Text.

The descriptions are in various lengths, but most of the 1000 entries
have a dimension entry.

I want to display the decimals in fractions and also format the
numbers to reflect inches. ie in. or the "

VBA is probably easiest, but anyone have an idea on how to do this??


Thanks

 

[Patrick Molloy]

a UDF could be used...here's somethign to get you started...though you'll
definitely need to fine tune it...

Option Explicit
Sub test()
MsgBox parser1("Widget 12.25 x 12.1875")
End Sub
Function parser1(text As String) As Variant
Dim pos As Long
Dim result As String
Dim prod As String
Dim hgt As Double
Dim wdth As Double
parser1 = text
If text Like "* x *" Then
pos = InStr(text, " x ")
pos = InStr(Left(text, pos - 1), " ")
result = Left(text, pos)
text = Mid(text, pos + 1)
pos = InStr(Left(text, pos - 1), " ")
hgt = Left(text, pos - 1)
text = Mid(text, pos + 3)
wdth = text
result = result & Format$(hgt, "0 #/8") & " x " & _
Format$(wdth, "0 #/8")
parser1 = result
End If
End Function

 

[srosetti]

a UDF could be used...here's somethign to get you started...though you'll
definitely need to fine tune it...

Option Explicit
Sub test()
    MsgBox parser1("Widget 12.25 x 12.1875")
End Sub
Function parser1(text As String) As Variant
Dim pos As Long
Dim result As String
Dim prod As String
Dim hgt As Double
Dim wdth As Double
parser1 = text
If text Like "* x *" Then
    pos = InStr(text, " x ")
    pos = InStr(Left(text, pos - 1), " ")
    result = Left(text, pos)
    text = Mid(text, pos + 1)
    pos = InStr(Left(text, pos - 1), " ")
    hgt = Left(text, pos - 1)
    text = Mid(text, pos + 3)
    wdth = text
    result = result & Format$(hgt, "0 #/8") & " x " & _
    Format$(wdth, "0 #/8")
    parser1 = result
End If
End Function

I've played a bit with the UDF. Cant seem to get it to do what I want
in the Column labeled desclong I'm not really versed in coding the
UDF.. any ideas on how to focus it to run on the one column?

 

[srosetti]

I've played a bit with the UDF.  Cant seem to get it to do what I want
in the Column labeled desclong  I'm not really versed in coding the
UDF.. any ideas on how to focus it to run on the one column?

Problem is I have some data I need in fractions. but when I copy the
fraction data into a text field it loses its number formatting and
turns back into decimal form.

I'll copy some real data from my spreadsheet so u can see whats going
on. *This is what I see after I put the fractions from column H4 L4 to
the end of text in column N4 'Widget'

H4 L4 N4
9 1/4 19 7/8 Widget 19.875" x 9.25"

I have thousands of entries so I don't want to do this by hand.. I've
tried everything I know to copy the fractions over, but no luck.
Someone wrote a UDF function earlier, but it didn't exactly work for
me. I'm trying to just simplify the steps by putting in the column H
and L which have the decimal form and I converted them to fractions..
My raw data is all in decimal so I have to work from that to get it to
fractions.

What I need my final data to look like is..

N4 Widget 19 7/8" x 9 1/4"

It would also be acceptable if the data ended up like

N4 Widget 19 7/8 x 9 1/4 inches

The quotes for inches is best, but if not possible I can use the word
inches too.

Thank You