If number in cell Then

[ScabbyDog]

Cell O10 will contain a name followed by either one, two or three numbers. So the cell could be "David Gold 28'," or it could be "David Smith 12', 77',"

The name will always be different. How can I look at cells O10 down as far as O30 and if a number is in the cell then take the number and put it in the cell to the right of where it was found. And if there are two numbers found then paste the 2nd number found 2 cells to the right.

So in my above example if O10 contained "David Smith 12', 77'," then cell P10 would be "12" and cell Q10 would be "77"

Slightly complicated but I hope you can help! Thanks!

[JBeaucaire]

If there always two-word names, this is pretty simple. If not, this gets nasty fast unless you resort to VBA. Since this is the VBA forum, what's your preference?

With all two-word names, simple formulas will do the job, no VBA is needed at all.

[JBeaucaire]

For instance, this formula in P10, then copied down and across through column R will extract the numbers based on your examples:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($P10, " ", REPT(" ", 100)), COLUMN(A1)*100+50, 100)),"'", ""), ",", ""), """", "")

[ScabbyDog]

The names could be any length and could be 3 or 4 or more names long like Joe John Mike O'Neill. Will have to be done witj VbA

[JBeaucaire]

Here's a UDF then... it strips out the commas, apostrophes and quotes from the original string in hopes of creating actual numeric values separated by spaces. If there's more garbage characters to eliminate, they can be added.

Please Login or Register  to view this content.

Install that in a normal code module. Then it's used in a cell like so:

P10: =Getnum($O10, 1)
Q10: =Getnum($O10, 2)
R10: =Getnum($O10, 3)

[ScabbyDog]

I'm looking forward to testing that out tomorrow. Will it work for 4 or more numbers beside the name, so;

P10: =Getnum($O10, 1)
Q10: =Getnum($O10, 2)
R10: =Getnum($O10, 3)
S10: =Getnum($O10, 4)
T10: =Getnum($O10, 5)
U10: =Getnum($O10, 6)

[JBeaucaire]

Sure will....

Now that you understand it, you could go with this in P10 and copy down and across the columns/rows....don't edit it:

=Getnum($O10, COLUMN(A1))

[ScabbyDog]

Just tested it. Works really well. Thank you! Sometimes there will be a funny sort of apostrophe in there as well that is not the normal apostrophe. I would paste it in here so you could alter the code to include it but I don't have access to the PC where the character is saved. I will try it out tomorrow!

[JBeaucaire]

just expand the UDF to add a new REPLACE to this line of code, the REPLACE is added at the start, then the character to remove is added at the end:

CleanString = Replace(Replace(Replace(Replace(MyCell.Cells(1), ",", ""), "'", ""), """", ""),"PutItHere", "")

[ScabbyDog]

Thanks! Works great with the new character added. My last issue now is making a formula that looks for the smallest number within a range (P10:U20) and when it finds the smallest number puts the number in cell a5 and if it was found in say P12 then it would return whatever is in O12 in cell A2. If the smallest number was found in U18 then it would return whatever is in cell O18 in cell a2.

[JBeaucaire]

Sounds like a new thread is in order.

[ScabbyDog]

True it does! Thanks JB. Link to new thread if interested

http://www.excelforum.com/excel-gene...81#post3164581