Search Function 2

[Lagoon1]

Hi
Need some help with a formula.

Cell A1 - Adjust by / 0.5% and total
Cell A2 - Adjust by / 1% and total
Cell A3 - Adjust by 10.5% and total
Cell A4 - Adjust by 110.5% and total

I need a formula that extracts just the value and % from the above cells. I've used the search function but seems to be missing something with the wildcard.

Can someone please help out.

Thanks.

Regards,

Lagoon1

 

[JoJ]

Not sure if this is what you want, but have you tried something this:

=MID(A1,SEARCH("by / ",A1)+5,SEARCH("%",A1)-SEARCH("by / ",A1)-5)


I used 5 because it is the length of your search text.

Hope this helps you.

 

[SkipVought]

Hi,

If the data were in column B, then in A1
[tt]
=B1*COUNT($B$1:B1)*0.5/100
[/tt]
and copy down.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Lagoon1]

Hi Guys
Unfortunately, the functions above does not work.

Any other ideas?

Thanks.

Regards,

lagooon1

 

[Lagoon1]

Hi JOJ
Sorry, your formula works but it does not include the percentage. Anyway, I had worked around it with the cell formatting.

By the way, why do you have to add 5 and then minus 5.

Although the formula works, can you please kindly brief me about your formula.

Thanks.

Regards,

lagoon1

 

[SkipVought]

what do you want the results to be -- examples

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Lagoon1]

Hi SkipVought
Example


Original Value

Cell A1 - Adjust by / 0.5% and total
Cell A2 - Adjust by / 1% and total
Cell A3 - Adjust by 10.5% and total
Cell A4 - Adjust by 110.5% and total

End Result

Cell b1 - 0.5%
Cell b2 - 1%
Cell b3 - 10.5%
Cell b4 - 110.5%

Thanks.

 

[JoJ]

Lagoon1,

The idea is to use the MID function to pick the given string of characters between "by / " and "%".

For the positional arguments of the MID function, embed 2 SEARCH functions. One for the starting point, and the other for the length argument of the MID function.

The start_num argument of MID should then be 5 characters more than where "by / " was found because "by / " is 5 characters long.

Hope this helps.

 

[SkipVought]

[tt]
Sub GetPct()
For Each r In Selection
For Each s In Split(r.Value)
s = Replace(s, "/", "&quot
s = Replace(s, "%", "&quot
If IsNumeric(s) Then
With r.Offset(0, 1)
.Value = s / 100
.NumberFormat = "0.0%"
End With
End If
Next
Next
End Sub
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Sorry,

I shoud have stated the second line this way
[tt]
for each r in range([A1], [A1].end(xldown))
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884