Extract Text from Cells - Intermediate Example

The attached workbook is simplified from my actual workbook.

On the Wins tab you will see the manual table of number of 1sts, 2nds, 3rds for each player, and next to it the calculated table. (The manual table is wrong, hence the need to move to a calculated table.)

The Wins tab uses an array VLOOKUP(col,table,{c1,c2,c3...}) to get the finished-in-what-place data for each player from the first worksheet and then dumps that into an array of cells. Then the calculated table does a COUNTIF() on that array of cells for each of 1st, 2nd, 3rd.

I thought it would be possible to have a simpler solution without the array of cells, conceptually:
COUNTIF(VLOOKUP(col,table,{c1,c2,c3...})
for each of 1st, etc, except that isn't a valid formula, likely because the array VLOOKUP() output isn't what COUNTIF() is expecting as input. Is there a way to get this to work? (I don't like the intermediate array of cells.)

Or, is there a better way to create the calculated table?

I've researched array formulas via google and have learned enough to know that often a VLOOKUP can be done equally well with something else.

I'm new enough to array formulas to not know what something else looks like.

I recognise that the current structure means one VLOOKUP and three COUNTIFs, and my desired solution without the array of cells means three VLOOKUPs.

...Stu

I am trying to write some VB code which can modify a cell's data. Column A contains the main string. What I want to do is look at each cell in Column A and if the slash character "/" exists then do one of two things:

1. If the string is "/L" then copy the "L and any text after that to the same row in column G
2. If the character after "/" is anything else then don't copy anything to Column G

Next, in the column A data, only keep the text preceeding the "/".
If there is no "/" in the cell, then do nothing.

Basically I have a column of serial numbers but only some of the cells have extra information which is displayed after the "/" character.

eg. If cell A1 has the string "aaa123/L001" , I want cell A1 to now read "aaa123" and cell G1 to read "L001"
eg2. If cell A1 has the string "aaa123/Server", I want cell A1 to now read "aaa123" and cell G1 empty.


Thanks for your help.

Can I extract text from cells... without double-clicking in each cell and copying and pasting?

See attachment.

Thanks,

I have to compare rule changes for a software program. The data exported gives me the new rule and the old. I want to find the differences between the new rule from the old rule and have the differences populate the adjacent cell. The rules are not identifical. I have added an example to my inquiry.

Utilizing the sample. The new rule is in Column A2 and the old rule in B2. Is there an equation I can use to extact the differences to cell C2. The answer would be "UYG,*STK,*WIREX,*BMFIX,* FBRUX,*HEPRU," I just don't want to visually examine all the different rules, just too many.

Thank you.

I have a cell with this text: 36-1/2" x 73". I need it to be listed into two different cells as 36.5 and 73. How do I remove the hyphens and inch marks and change the fraction to decimal?

A1 36-1/2" x 73"
B1 36.5
C1 73

Thanks!!

Good evening

I have a small query that I just can't seem to get my head around, hopefully someone can point me in the right direction.

I have a range of data as shown below and basically what I want to do is enter a number in say A5, I want to look at the low and high numbers in columns B and C and from this in B5 extract the text in column D. For example if i enter 4155 in A5, B5 will say direct expenses, if I enter 7020 it will say Overheads.

I had look at using if(and statement but this only works so far, not sure if this even the right way to go about it.


A B C D

1 Sales overseas 4100 4150 Direct Expenses
2 Sales Promotion 6200 6299 Direct Expenses
3 Gross Wages 7000 7099 Overheads
4 Rent and Rates 7100 7199 Overheads

If possible I would like to use a formula rather than code, but if this is the way to go then I will work with that.

Any help would be much appreciated.

Thanks in advance.

Hi, I am trying to separate text in columns into two columns. For example, I have a list of names in A1:A5 that I need to separate into B1:B5 and C1:C5. For example:

(A1) Tennessee-Martin at Jacksonville State
(A2) Villanova at Temple
(A3) South Carolina State at Central Michigan
(A4) North Texas at Florida International
(A5) Kentucky Christian at Morehead State

Would become:
(B1) Tennessee-Martin (C1) Jacksonville State
(B2) Villanova (C2) Temple
(B3) South Carolina State (C3) Central Michigan
(B4) North Texas (C4) Florida International
(B5) Kentucky Christian (C5) Morehead State

Thanks

I have a textbox on one sheet that has multiple paragraphs - blocks of text separated by carriage returns.

I want some vba code that would, on another sheet, extract the contents of the textbox and put each paragraph in a separate cell.

For example: Sheet(1) has TextBox1. In this textbox is the following block of text:

[StartText]
This is paragraph one with some text in it.

This is paragraph two with additional text in it.

And this is paragraph three.
[/StartText]

In Sheet(2), I would have the following after running the script:

Cell B1 = "This is paragraph one with some text in it."
Cell B2 = "This is paragraph two with additional text in it."
Cell B3 = "And this is paragraph three."

All of this would be done in a loop so that the number of actual paragraphs in the textbox is dynamic and I could control the destination cells.

I've seen some forum entries that show how to extract the entire block of text out of a textbox into a single cell, but not one cell per paragraph (or carriage return).

Thanks in advance.

Troy

I have a column of cells over 2000 rows that have a bunch of text in each cell. In the middle of this text in each cell are websites (not hyperlinked). I was wondering if someone wrote a macro or formula to extract URLs located in a bunch of text? I can parse this out but doing some delimiting but that will take longer.

does anybody know how to find an intermediate value on a scatter plot w/ a
line following the curve...not a trendline but just a well i guess u'd call
it a trace line. Basically I have a few numbers (two columns, one x, one y)
which creates a "utility" curve just a line connecting those dots. I'm
simply tring to find a way to ask it a number and have it return the
corresponding value along the curve... so if i gave it a number off the x
axis it would give me the y axis value.