# Transferring IF/FIND/MID Formula from Excel to Access Form

#### mcguitar

##### New Member
Good day,

I have something that works in Excel, and I am working on transferring it to Access.

In Excel, I can paste a selection of data in one cell (M2), and cells M5 - M18 pulls data from that one cell using IF, FIND and MID formulas. I am using the FIND formula to find the position of the specific text, then the MID formula to pull the data.

=IF(M2="","",MID(M2, (FIND("%", M2, 1)+1), (FIND("*", M2, 1)-1)-(FIND("%", M2, 1)+1)))
or
=IF(M2="","",MID(M2, (FIND("\$", M2, 1)+1), 5))

I can use the MID formula, but when implementing the FIND portion, it errors out. I have replaced the M2 with [Text200] as this is the form data where the information is input.

How do I translate this to Access friendly forms? Thanks all!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
In Access, you use IIF instead of IF.
And you can use the INSTR function in place of FIND (see: MS Access: Instr Function).

Armed with that knowledge, you should be able to adapt your formula.

Replies
3
Views
252
Replies
3
Views
114
Replies
2
Views
307
Replies
3
Views
347
Replies
1
Views
383

1,202,989
Messages
6,052,945
Members
444,620
Latest member
marialewis16

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back