Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to force column formatting (text, date, etc) when loading a fixed width text file into an array? | Excel Programming | |||
Find formatting in text in cell, insert tags around formatting. | Excel Programming | |||
Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Formatting text in a user form text box | Excel Programming |