Best formula to use?

[nwatters]

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.

 

[Joyner]

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?

 

[Joyner]

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.

 

[nwatters]

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.

 

[Joyner]

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?

 

[nwatters]

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.

 

[nwatters]

... 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!