YouTubersLoveExcel#15: Mixed Number formulas and Format

I have cells containing text such as 100P1, 10P1, 1P1, 100PB10, etc. I would like to pad the leading set of numbers to 3 places and the trailing set of numbers to 3 places by adding leading zeros where needed to each set of numeric characters. Note that the length of the text portion of the string can vary in length.

Can anyone help me by providing a function to accomplish this, or is that beyond the scope of the forum?

Thank you for any help that can be provided!

Cell 'Yield of 100'!O4=1/6 format_fraction

Cell 'Yield of 100'!P4=" Pie"

Cell 'Line Item card'!12:h12=("Portion Size: ")&(ROUND('Yield of 100'!O4,2))&(" ")&('Yield of 100'!P4)

Cell 'Line Item card'!12:h12 displays as "Portion Size: 0.17 Pie"

desired cell display for Cell 'Line Item card'!12:h12 is "Portion Size: 1/6 Pie"

I'm not at all bad with excel but just cant get this to work and i have several like this i want to get fixed. spend several days getting this far.

You can download 2003 example at http://www.2shared.com/file/8438868/...CONVERTER.html

Hi. If I use:

=DATE(2010,7,12)

I get: 7/12/2010.

However, if I use:

=DATE(2010,7,12) & " - " & DATE(2010,7,19)

The date gets reformatted. I get: 40371 - 40378

Can someone tell me how to get: 7/12/2010 - 7/19/2010

Hi All.
Newbie first question - and I can't get the html bit to work in Excel, so you'll have to use your imaginations!!

I've created a graph, finally, which looks exactly as I want it.

For my sins, it has two sets of (vertical) stacked columns and a single column, from the first Y axis.

It has two sets of points (x-y style) from the second Y axis. All well and good.

But, I can't get the points to join together with lines - like a line graph!!
(I have tried X-Y and Line Graph types - neither help!)

I have clicked on the data set, and formatted the data series to add a line, and in the legend, it shows a little point with a line through it. But the line does not appear on the graph.

Any ideas?

Was that even remotely clear??!! Thanks!!


EDIT: TO ANSWER THIS QUESTION, PLEASE USE KRISTY'S LINK BELOW! THANKS!

Hello,

Is it possible to separate the parts of a mixed number? I have a cell in which is generated a decimal number. I can convert this to a mixed number. What I need to do from there is extract the whole number and the numerator. Is there a way to do this?

Thank you,
Tom

I need a worksheet formula to determine the next value in a series that contains both a letter and number. For example, Col A consists of P10101, P10102, P10103, etc. Col B is a description of the proposed work represented by Col A. When I enter something in the next open Col B, I want Col A to show P10104. The tricky part is that users can sort the file so Col A is no longer "in order", and there may be hundreds of P-designations so it is not always this obvious what the next value should be. Is there a formula I can use in Col A to strip the numeric part out of all of the previous entries, find the max value and increment it, then add back the "P"?
Thanks in advance for help!

Forum Sample 27 Dec 09.xls
Hi Forum Friends
Please see the attachment.
I want to match the number in D4 to the numbers on the right in Column F21: F80, and put the offset result in E4.
The end result I need is as computed in E6, but without referring to a specific cell in the column.
Many Thanks
Al

I am trying to extract a 7-consecutive-digit number from a mixed string (letters and numbers). The only condition is that the number has to start with a "7" or "6" and be exactly 7 consecutive numbers long. My function only evaluates for one and one within the other but not both. It's faulted because it only looks for the occurrance of "6". rCell is the string range.

Function ExtractNumber(rCell As Range)

If Len(Mid(rCell, InStr(1, rCell, "6"))) > 6 Then
ExtractInvoice = Mid(rCell, InStr(1, rCell, "6"), 7)
ElseIf Len(Mid(rCell, InStr(1, rCell, "7"))) > 6 Then
ExtractInvoice = Mid(rCell, InStr(1, rCell, "7"), 7)
End If

End Function

I have a series of numbers that is contues for orders. However depending on the type of order the prefixed letter change.

I need to sort by this cell by in numerical order only

The possibilies are

TBSP0000
TBW0000
TBR0000


However due to a back order system these numbers can become


TBSP0000/1
TBW0000/2
TBR0000/3

When sorted I would like it to look like

TBR1111
TBSP1112
TBSP1112/1
TBSP1113
TBR1114
TBW1115
TBSP1116
TBSP1117
TBSP1118
TBW1119
TBR1120
TBR1120/1


Any help appreciated.

Hi there,

I'm having an annoying issue merging from Excel 2000 to Word 2000 from a column that has either a number or text. Specifically, my source spreadsheet has a column (with the header 'Stage') that draws its data from a column in a different worksheet in the same document, using a basic formula that reads:

=IF('Scores Assessment'!I10<>"",'Scores Assessment'!I10,"")

The column Scores Assessment referenced in the above formula can have one of 5 values, which a 1, 2, 3, 4, or TOEFL (these are class levels at our school).

When I merge into the Word document, the Stage information merges fine for the numbered stages (1-4) but for the value 'TOEFL' I get a 0 (zero). I've tried switching the formatting of the original data column and/or the merged column to Text, General, and Custom, but I get the same result.

In the Word doc the merge info for this item is:

{ MERGEFIELD "Stage" }

I've tried removing the quotes around "Stage" in that, but it didn't help either.

I'm sure the solution is simple, but I can't figure it out. Unfortunately, I can't seem to attach the relevant files on here--the file sizes are too large.

Thanks for any tips!