Good Morning, I have a worksheet that I downloaded from my banking statement and one column contains several pieces of text that I would like to put into separate excel columns.
DEBIT CARD PURCHASE XXXXX1937 AMC THEATRES ONLINE XXXXX4262 KS
CORPORATE ACH 464348001EPAYK ATT PAYMENT
N0608 1937 PAYMENT POS00000101 0827719 AMAZON.COM SEATTLE WA
ACH WEBRECUR CKFXXXXX1324POS PNC CONSUMER LN ONLINE PMT
The examples above (ALL LOCATED IN COL A) each has a double space after the first column of information and I need to go from the left side and take all text up to the double space and place it in the first empty column next to this column (COL B)
DEBIT CARD PIURCHASE
CORPORATE ACH
N0608 1937 PAYMENT
ACH WEBRECUR
Next I need to take the next piece of information, after the double (or more) space and go over to the right until the last number is encountered and put this information into Col C. Some of these have a number and then a letter with no spaces, and some have spaces that can be used as the end. Examples show end product desired. but I need to seperate the number and letter.
(COL C)
XXXXX1937
464348001
POS00000101
CKFXXXXX1324
Next I need the next section of text that ends with a double space placed in the next column (COL D). Double space should be the only requirement to stop this relocation of text.
AMC THEATRES ONLINE
EPAYK ATT
0827719 AMAZON.COM
POS PNC CONSUMER LN ONLINE PMT
Next I want everything else remaining to be moved to column (COL E)
XXXXX4262 KS
PAYMENT
SEATTLE WA
NO ENTRY HERE FOR THIS COLUMN.
I would need a button to initiate the code with the following notes, I think. I will start off with copying the material above and pasting it into column a which will remain selected. Leaving it selected I would activate the button for this macro and it would display a message box asking me to confirm that A22 through A89 (EXAMPLE ONLY) was the selected area to perform this macro. I would click yes and it would run the macro on the selected cells and place the data as indicated in the appropriate columns that I've laid out above. Also anywhere there were multiple spaces I would like to trim tose spaces down to only one.
I've been able to get the first column with a function but I'm lost after that and figured that vba code would be a lot easier Is there anyone that might be able to help me with this. The information is taken from my online bank and saved in csv format and then resaved as an xlsm file. I can provide the actual data for the column A if anyone would need it to solve this problem for me.
Thanks so much for the anticipated help I'll receive and HAPPY FATHER'S DAY TO ALL.
Bob Reynolds
DEBIT CARD PURCHASE XXXXX1937 AMC THEATRES ONLINE XXXXX4262 KS
CORPORATE ACH 464348001EPAYK ATT PAYMENT
N0608 1937 PAYMENT POS00000101 0827719 AMAZON.COM SEATTLE WA
ACH WEBRECUR CKFXXXXX1324POS PNC CONSUMER LN ONLINE PMT
The examples above (ALL LOCATED IN COL A) each has a double space after the first column of information and I need to go from the left side and take all text up to the double space and place it in the first empty column next to this column (COL B)
DEBIT CARD PIURCHASE
CORPORATE ACH
N0608 1937 PAYMENT
ACH WEBRECUR
Next I need to take the next piece of information, after the double (or more) space and go over to the right until the last number is encountered and put this information into Col C. Some of these have a number and then a letter with no spaces, and some have spaces that can be used as the end. Examples show end product desired. but I need to seperate the number and letter.
(COL C)
XXXXX1937
464348001
POS00000101
CKFXXXXX1324
Next I need the next section of text that ends with a double space placed in the next column (COL D). Double space should be the only requirement to stop this relocation of text.
AMC THEATRES ONLINE
EPAYK ATT
0827719 AMAZON.COM
POS PNC CONSUMER LN ONLINE PMT
Next I want everything else remaining to be moved to column (COL E)
XXXXX4262 KS
PAYMENT
SEATTLE WA
NO ENTRY HERE FOR THIS COLUMN.
I would need a button to initiate the code with the following notes, I think. I will start off with copying the material above and pasting it into column a which will remain selected. Leaving it selected I would activate the button for this macro and it would display a message box asking me to confirm that A22 through A89 (EXAMPLE ONLY) was the selected area to perform this macro. I would click yes and it would run the macro on the selected cells and place the data as indicated in the appropriate columns that I've laid out above. Also anywhere there were multiple spaces I would like to trim tose spaces down to only one.
I've been able to get the first column with a function but I'm lost after that and figured that vba code would be a lot easier Is there anyone that might be able to help me with this. The information is taken from my online bank and saved in csv format and then resaved as an xlsm file. I can provide the actual data for the column A if anyone would need it to solve this problem for me.
Thanks so much for the anticipated help I'll receive and HAPPY FATHER'S DAY TO ALL.
Bob Reynolds