Formatting text
 

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

Formatting text
 

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" wrote:

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
.

Formatting text
 

On Nov 3, 2:54*am, Patrick Molloy
wrote:
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" wrote:
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
.



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?

Formatting text
 

On Nov 3, 10:04*am, srosetti wrote:
On Nov 3, 2:54*am, Patrick Molloy



wrote:
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" wrote:
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
.

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