Best formula to use?

nwatters

New Member
Joined
Aug 6, 2014
Messages
8
Hi,

I'm trying to find the best way to do the following:

I have a spreadsheet where I've downloaded my bank data. The Description column contains things like the card number, Receipt number etc. What I'm hoping to do is take the data in that cell, and copy it into another column. For instance, I want to find the word "Receipt", and take the following 6 characters, and dump them in a certain column. The same for the Card Number; find the phrase "Card ***** XXX" and dump that data into another column.

I've tried Vlookup, H Lookups etc but none have worked properly.

I don't know enough about Excel formulas to be able to know the best thing to do here.

Any advice?

N.
 
In your example for the Card, I don't see anything to extract after the card number, or is it the actual Card number you want?
 
Upvote 0
I think something like this is what you will want, may need to be tweaked a bit based on what you want for "Card"

=IFERROR(MID(B1,FIND("Card",B1)+17,6),IFERROR(MID(B1,FIND("Receipt",B1)+8,6),""))

This assumes "Card" won't be in the cell for "Receipt". I see Receipt is in the cell with "CArd", if you need both "Receipt" and "Card" from those cells, the formula would be a bit more complicated and possibly an array formual.
 
Upvote 0
wow perfect for the receipt one, thanks!

The Card one is specifically "Card ************1234"

And yes, the column H contains things like card number and receipt in the same cell going down the column.
 
Last edited:
Upvote 0
So I'm not clear, what do you want returned for this example:

Cash & Purchase - Glenbrook IGA* Gle nbrook - Receipt 323944 ATM owner fee of $2.50 charged by Glenbrook IGA* Gle nbrook Date 27/06/2010 Time 2:39 PM Card ************XXXX

It has a receipt number and a card number second. Currently my formula will return the last four numbers of the Card, what should be returned?
 
Upvote 0
What I want is in another column, it will still refer to the same cell, but using a different formula, to extract the last four numbers of the card. So in essence, a similar formula to the Receipt one, but this formula would look for the Card one, and copy the last four digits into the column.

So I'd be extracting "XXXX" to another column.

I'd be ONLY looking for that information in this formula.

I'm simply trying to extract certain data to other columns.
 
Upvote 0
... and I did just that, and it works! Thank you so much for your help. I have learnt something new now, and can take that and learn more and expand what I'm after.

Thanks again! :)
 
Upvote 0

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top