Find and Replace question

[Z0827]

I have a LOT of data that I pulled a report on. It is in columns. One column has notes inside of it and previously I ran a query with access to pull the numbers out into the next column. Some of these numbers didn't pull because they include 1-4 or 2-3 type numbers. Is there a way to find the cells that have numbers like that and replace them into the next cell with a different number?

For instance if in cell R493 is says 2-3, I want to put 2.5 into cell S493. I could do it manually, but there will be a total of around 20 thousand lines to look at. Any easy way to do this?

Thanks!

 

[MrKowz]

This is a relatively simple macro, however I need just a little more info:

• What value do you want populated with 1-4?
• Are there any other combinations?

 

[Gerald Higgins]

You might also be able to do it with a simple text formula, IF the hyphen characters "-" are ONLY ever present in the number format you describe, with 1 digit either side.

It will get more complicated if you sometimes have entries like this
9-10
12-13
99-100
Dave Smith-Jones 8-9
and so on....

 

[Z0827]

This is a relatively simple macro, however I need just a little more info:

• What value do you want populated with 1-4?
• Are there any other combinations?

There could be more.

1-2 replace with 1.5
1-3 replace with 2
1-4 replace with 2.5
2-3 replace with 2.5
2-4 replace with 3
3-4 replace with 3.5

Thanks. I appreciate the help.

 

[MrKowz]

This could be handled with a formula, I believe:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">1-2</td><td style="text-align: right;;">1.5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">1-3</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">1-4</td><td style="text-align: right;;">2.5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">2-3</td><td style="text-align: right;;">2.5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">2-4</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">3-4</td><td style="text-align: right;;">3.5</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">FIND(<font color="Green">"-",A1</font>)</font>),"",AVERAGE(<font color="Red">LEFT(<font color="Green">A1,FIND(<font color="Purple">"-",A1</font>)-1</font>),RIGHT(<font color="Green">A1,LEN(<font color="Purple">A1</font>)-FIND(<font color="Purple">"-",A1</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Z0827]

I will try it out. Thanks.

 

[Z0827]

I am trying this in cell S2292 with the reference cell being R2292

=IF(ISERROR(FIND("-",R2292)),"",AVERAGE(LEFT(R2292,FIND("-",R2292)-1),RIGHT(R2292,LEN(R2292)-FIND("-",R2292))))

I get #value in my cell where I enter the formula. Did I enter something wrong?

 

[MrKowz]

What value is in R2292?

 

[Z0827]

What value is in R2292?

I guess I didn't explain enough. That is my fault sorry. There is actually a few sentences in R2292. It looks like this:

General -- Group therapy provided with constant attendance to 2-3 patients with focus on UBE in standing for 5' and seated for 10' at level 3 in order to improve activity tolerance and balance.. For functional mobility tasks.

So I want to just focus on the #-# part. I don't think I explained that in the beginning. Sorry.

 

[MrKowz]

I guess I didn't explain enough. That is my fault sorry. There is actually a few sentences in R2292. It looks like this:



So I want to just focus on the #-# part. I don't think I explained that in the beginning. Sorry.

Ahh, yes that is much more complicated. The problem is going to be determining if there is a number before and after the hyphen, as well as how many digits that number is. Let me play around with this and see what I can come up with.