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]

Hello, and welcome to the board.

Using MID with FIND will probably do the trick. May need to know a bit more about the data to use the proper extraction. For REceipt do you always want to get the next six characters and same for Card, is it always as you lauy out "Card ***** XXX"?

 

[Momentman]

Welcome to the board

Could you maybe post SAMPLE data and what the obtained results should be it appears we need more info

 

[nwatters]

Hi Joyner,

Thanks for the welcome. For Receipts, the format is always "Receipt - XXXXXX" but not necessarily in the same place (ie the same amount of characters from the beginning of the cell). For the Card Number, they always display it like "Card **** XXXX" (the last four numbers of the card number, just made X's in here for security's sake.) So for the card numbers, dumping the whole phrase would be handy, but if not, just the last 4 digits would do.

I'm not sure yet how to attach spreadsheets

 

[Joyner]

For Receipt do you want this returned - "Receipt - XXXXXX" or just the "XXXXXX" numbers, and for the card same thing, and if just the "XXXXXX" part does that follow like Receipt "-XXXXXX"

Some actual "Dummy" data and expected results would help.

Thanks

 

[Momentman]

Do you mean something like this

Excel 2010
	A	B
1	Receipt - 124566	124566
2	Card 54657699713564	3564
Sheet5

Cell Formulas
Range	Formula
B1	=RIGHT(A1,6)
B2	=RIGHT(A2,4)

 

[nwatters]

Receipts I'm happy with just the numbers, the card details the same. the column heading I'm copying to will have a descriptor accordingly in it, so I shouldn't need the name.

I tried copying an image to the last post but it came out all jumbled. I tried copying text, but it was all out of alignment.

Any advice there? I'm very new to forums themselves...

 

[nwatters]

Not quite, as that works on them being in the same spot in the cell. They aren't in the same spot.

 

[nwatters]

OK I think this is the best I can get for now...
Column A is date, column B is Description etc

[TABLE="width: 787"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Receipt No[/TD]
[TD]Card No[/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2010[/TD]
[TD]Direct Credit/Debit Fees[/TD]
[TD="align: right"]$0.40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2010[/TD]
[TD]St.George & Banksa Atm Fees[/TD]
[TD="align: right"]$0.60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2010[/TD]
[TD]Eftpos Fees[/TD]
[TD="align: right"]$1.20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2010[/TD]
[TD]WORK PAY [/TD]
[TD][/TD]
[TD="align: right"]$800.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2010[/TD]
[TD]Visa Purchase 28May[/TD]
[TD="align: right"]$50.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21/06/2010[/TD]
[TD]Deposit - WORK PAY Receipt 103586[/TD]
[TD][/TD]
[TD="align: right"]$800.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21/06/2010[/TD]
[TD]Deposit - Initial Deposit - Receipt 101366[/TD]
[TD][/TD]
[TD="align: right"]$500.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/06/2010[/TD]
[TD]Direct Debit - GYM - Receipt 101062 EZYPAY ID 1117745[/TD]
[TD="align: right"]$30.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27/06/2010[/TD]
[TD]ING DIRECT - ATM Rebate - Receipt 323944[/TD]
[TD][/TD]
[TD="align: right"]$2.50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27/06/2010[/TD]
[TD]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[/TD]
[TD="align: right"]-$252.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes sense?

 

[Joyner]

For the Receipt this should work if laid out as you show:

=MID(B25,FIND("Receipt",B25)+8,6)

For the Card number is it laid out like this, exactly?:



"Card **** XXXX - XXXXXX"