Excel formula(s) headache

[raybo]

Ok, you Excel gurus, here's a challenge. I'm pretty good with writing formulas, but, this one's got me confounded.

Without using macros, how would you write a formula/formulas that will look in a single column of 20 cells, some of which are blank cells and some have numbers/text in them, and return each cell that contains numbers/text and present each of them, in individual cells, somewhere else?

So, if I have only 2 of the 20 cells that are not blank, I want the 2 non-blank cells' values presented in 2 separate cells, in another area.

[Charli125]

I would just use a pivot table and sort it by anything. That way the cells with text in them will come to the top.

[raybo]

I would just use a pivot table and sort it by anything. That way the cells with text in them will come to the top.

Thanks for the suggestion, but, wouldn't that involve manually having to sort the table? Then, when that race is done, having to return the table to it's original format? I'm trying to not add any manual steps to the process. If I can use formulas, there are no manual steps, the values will automatically appear where I want them.

Also, the values have underlying formulas, in the values' respective cells, and sorting might (would?) screw up the formulas.

Automation is key here.

[Charli125]

Good point, you would have to right-click and refresh.

I really don't see any way to do that without using a small macro to refresh whenever the data changes.

Sorry I can't be of help.

[raybo]

Good point, you would have to right-click and refresh.

I really don't see any way to do that without using a small macro to refresh whenever the data changes.

Sorry I can't be of help.

Nothing to be sorry about, I'm fully prepared to write/record a macro (or macros) to automate it, if I am forced into it. Just thought there might be a way to solve the quandary via formulation rather than macros.

[TrifectaMike]

Ok, you Excel gurus, here's a challenge. I'm pretty good with writing formulas, but, this one's got me confounded.

Without using macros, how would you write a formula/formulas that will look in a single column of 20 cells, some of which are blank cells and some have numbers/text in them, and return each cell that contains numbers/text and present each of them, in individual cells, somewhere else?

So, if I have only 2 of the 20 cells that are not blank, I want the 2 non-blank cells' values presented in 2 separate cells, in another area.

Use the RANK function with 20 cells as your reference. For example,

=RANK(A1,$A$1:$A$20) in Cell B1, etc.

[raybo]

Use the RANK function with 20 cells as your reference. For example,

=RANK(A1,$A$1:$A$20) in Cell B1, etc.

That won't work because these are horse numbers and some horse numbers contain a number and a letter. Besides, even if they were just numbers, you would still end up with a column containing 18 errors and 2 numbers. I have 18 blank cells and 2 values now, I want to get to having 1 of the 2 values in a cell and the other value in a different cell.

The closest I can come to extracting the 2 numbers is to get them both to display in a single cell, separated by a space, using the "trim" function coupled with the "concatenate" function, where I concatenate all 20 cells, with spaces between each of the 20 cell values and the "trim" function removes all the spaces except the space between the 2 values.

So, if I had horse #5 in one of the 20 cells and horse #1a in another of the 20 cells, I would get this in the cell with the "trim"/"concatenate" formula in it:

5 1a
or
1a 5
depending on which horse appears first in the column).

This will work, for now, until (if) I can figure out a way to get them into 2 separate cells.

[TrifectaMike]

That won't work because these are horse numbers and some horse numbers contain a number and a letter. Besides, even if they were just numbers, you would still end up with a column containing 18 errors and 2 numbers. I have 18 blank cells and 2 values now, I want to get to having 1 of the 2 values in a cell and the other value in a different cell.

The closest I can come to extracting the 2 numbers is to get them both to display in a single cell, separated by a space, using the "trim" function coupled with the "concatenate" function, where I concatenate all 20 cells, with spaces between each of the 20 cell values and the "trim" function removes all the spaces except the space between the 2 values.

So, if I had horse #5 in one of the 20 cells and horse #1a in another of the 20 cells, I would get this in the cell with the "trim"/"concatenate" formula in it:

5 1a
or
1a 5
depending on which horse appears first in the column).

This will work, for now, until (if) I can figure out a way to get them into 2 separate cells.

Ok...I get it now.
Since you already have the data in a single cell, let's simulate a split function.

In cell B1
=LEFT(A1,FIND(" ",A1,1)-1)

This formula says, Start from the Left in cell A1, give me as many characters that exist to the first space encountered, minus one. This formula would give you 5.

In cell C1
= RIGHT(A1,LEN(A1) - FIND(" ",A1,1))

This formula says, Start from the right in cell A1, subtract the position
of the space in A1 from the length of the content of A1. This formula gives you 1a.

I think this should work.

Mike

[raybo]

Ok...I get it now.
Since you already have the data in a single cell, let's simulate a split function.

In cell B1
=LEFT(A1,FIND(" ",A1,1)-1)

This formula says, Start from the Left in cell A1, give me as many characters that exist to the first space encountered, minus one. This formula would give you 5.

In cell C1
= RIGHT(A1,LEN(A1) - FIND(" ",A1,1))

This formula says, Start from the right in cell A1, subtract the position
of the space in A1 from the length of the content of A1. This formula gives you 1a.

I think this should work.

Mike

Thanks Mike!!

You put me on the right track. Your formulas worked fine except I had to add some error catchers to them.

Here are the modified formulas that split the 2 values in cell "K25" into 2 separate target cells, "K28" and "K29":

formula in target cell "K28":

=IF(20-COUNTIF(K3:K22,"")=0,"",IF(20-COUNTIF(K3:K22,"")=1,K25,LEFT(K25,FIND(" ",K25,1)-1)))

formula in target cell "K29":

=IF(20-COUNTIF(K3:K22,"")=0,"",IF(20-COUNTIF(K3:K22,"")=1,"",RIGHT(K25,LEN(K25)-FIND(" ",K25,1))))

The mods check to see if: 1. there are values in the range, at all. 2. if there is more than 1 value in the range. Without these mods you get errors in 1 or both of the 2 target cells.

[TrifectaMike]

Thanks Mike!!

You put me on the right track. Your formulas worked fine except I had to add some error catchers to them.

Here are the modified formulas that split the 2 values in cell "K25" into 2 separate target cells, "K28" and "K29":

formula in target cell "K28":

=IF(20-COUNTIF(K3:K22,"")=0,"",IF(20-COUNTIF(K3:K22,"")=1,K25,LEFT(K25,FIND(" ",K25,1)-1)))

formula in target cell "K29":

=IF(20-COUNTIF(K3:K22,"")=0,"",IF(20-COUNTIF(K3:K22,"")=1,"",RIGHT(K25,LEN(K25)-FIND(" ",K25,1))))

The mods check to see if: 1. there are values in the range, at all. 2. if there is more than 1 value in the range. Without these mods you get errors in 1 or both of the 2 target cells.

I'm happy to hear it worked for you. Have a GREAT day.

Mike

[raybo]

I'm happy to hear it worked for you. Have a GREAT day.

Mike

You too!

Thanks again for your help!!!