Text to columns in VBA with multiple pieces of text

[rjrsn]

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

 

[oldbrewer]

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

is this all in a single cell ?
mark double spaces as ## so we can see them

 

[Rick Rothstein]

mark double spaces as ## so we can see them

By default, your first icon in the Reply window should be highlighted... if it isn't, click it. Once it is highlighted, if you click "Reply With Quotes", you will see the original formatting for the message (double spaces and all).

 

[oldbrewer]

testing rick where is reply with quotes please ?

 

[oldbrewer]

testing rick where is reply with quotes please ?

ah I found it

 

[rjrsn]

All in a single cell and here are the ##
DEBIT CARD PURCHASE## XXXXX1937 AMC THEATRES ONLINE## XXXXX4262 KS
DEBIT CARD PURCHASE## XXXXX1937 AMC THEATRES ONLINE## XXXXX4262 KS
RECURRING DEBIT CARD##XXXXX7169 PLANET FITNESS##XXXXX4050 NC
ACH WEB-SINGLE GASTONIASHEETMET WEBPAYMENT NONE IN THIS LINE

 

[oldbrewer]

now show us what you want to see from the example in post number 6

 

[rjrsn]

now show us what you want to see from the example in post number 6

Refer post #1 COL A is what I have
Col B, C, D & E are broken down to what I need in each one.

COL B COL C COL D COL E
DEBIT CARD PURCHASE XXXXX1937 AMC THEATRES ONLINE XXXXX4262 KS
DEBIT CARD PURCHASE XXXXX1937 AMC THEATRES ONLINE XXXXX4262 KS
RECURRING DEBIT CARD XXXXX7169 PLANET FITNESS XXXXX4050 NC
ACH WEB-SINGLE GASTONIASHEETMET WEBPAYMENT

Please note there is one exception to all of this and it is the 4th line. There are no double spaces and the entry will always be the same as it is listed. I think an "IF" the entry is ACH WEB-SINGLE GASTONIASHEETMET WEBPAYMENT Then place the ach web-single in col B
Place the GASTONIASHEETMET in col D
and the WEBPAYMENT in col E. This is the only exception and would like it dealt with that way.

Thanks so much for the time an effort.
Bob

 

[Rick Rothstein]

Refer post #1 COL A is what I have
Col B, C, D & E are broken down to what I need in each one.

COL B COL C COL D COL E
DEBIT CARD PURCHASE XXXXX1937 AMC THEATRES ONLINE XXXXX4262 KS
DEBIT CARD PURCHASE XXXXX1937 AMC THEATRES ONLINE XXXXX4262 KS
RECURRING DEBIT CARD XXXXX7169 PLANET FITNESS XXXXX4050 NC
ACH WEB-SINGLE GASTONIASHEETMET WEBPAYMENT

Please note there is one exception to all of this and it is the 4th line. There are no double spaces and the entry will always be the same as it is listed. I think an "IF" the entry is ACH WEB-SINGLE GASTONIASHEETMET WEBPAYMENT Then place the ach web-single in col B
Place the GASTONIASHEETMET in col D
and the WEBPAYMENT in col E. This is the only exception and would like it dealt with that way.

A few questions for clarification...

1) You show some ## signs for your data in Message #6 which do not appear in your breakout in Message #8... are they really there and, if so, should we be deleting them?

2) Your exception has the words "NONE IN THIS LINE" at the end of the text in Message #6 which does not appear in your breakout in Message #8... are those words really and always there and, if so, should we be deleting them?

3) In Message #1 you showed these...

CORPORATE ACH 464348001EPAYK ATT PAYMENT
N0608 1937 PAYMENT POS00000101 0827719 AMAZON.COM SEATTLE WA
ACH WEBRECUR CKFXXXXX1324POS PNC CONSUMER LN ONLINE PMT

which do not have X's at the beginning like you show in Message #6... also the first and third one have text at the end of the number which is not retained with the number... also the last tow have leading letters (other than X's) which do stay with the number.... so my question is this, what is the "rule" for identifying the number part that will go into Column C?