Extract date, text, and value

[MAP]

Hello Excel experts.
I am a beginner using Excel 2003... requesting help with a formula that will extract the date, certain text, and a dollar value from a string of text I get from a text file from my bank. I will be pasting the text into Excel 2003 starting in A1 and possibly down to A300.

example... A1 contains "04/21/2015______Account transfer______Transfer from CKH xxxx1234______$100.00______$1,100.00"

The date will always be the first 'column' in this string and has a fixed number of digits. The 'type' will be next preceded by a few spaces and it will have various character length. The 'description' will come next, again preceded by a few spaces, and it will also have various character length. The next 'column' contains the debit or credit value. The last 'column' is the balance value - both of these last 'columns' are separated by a few spaces.

I would like to know what formula I can put in B1 to extract the date from A1, in C1 to extract the Type from A1, in D1 to extract the description from A1, and in E1 to extract just the debit/credit value from A1. I have no use of the balance value.

The tricky part for a novice like me is the variable length of 'type' and 'description', and ultimately the non-fixed location of the debit/credit value.

I will appreciate your assistance with an Excel 2003 formula because I have no clue how to use VBA. However, if you would be kind to offer a VBS script that can read the text file and extract the information to fill in the cells... and help me understand it, it will help me as I attempt to learn the marvels of the spreadsheet.

I thank you for you kind assistance

 

[Jonmo1]

Are you saying the break between each section is ALWAYS multiple spaces? Is it a specific number of spaces, or various?
Are the OTHER spaces (like between Account transfer), are those always just single spaces ?

 

[godsaaint]

Easiest way to do this I think would be to just use text to columns (you find it in the data tab/menu). Choose "Delimited" by "Other", type _, and check "Treat consecutive delimiters as one"

EDIT: my bad, I misread, you have spaces, not underscores. You can still use text to columns. instead of delimited, use fixed and put the break lines yourself. you only have a few columns, so doing it manual shouldn't be a bother.

 

[Rick Rothstein]

Easiest way to do this I think would be to just use text to columns (you find it in the data tab/menu). Choose "Delimited" by "Other", type _, and check "Treat consecutive delimiters as one"

EDIT: my bad, I misread, you have spaces, not underscores.

You have the right idea, though... all the OP needs to do is use Excel's Replace dialog box to replace double spaces with some unique character, then still using the Replace dialog box replace that unique character in combination with a space (so no leading spaces will remain) with the unique character by itself, then use Text To Columns specifying that unique character in the Other field and making sure the "Treat consecutive delimiters as one" checkbox is checked.

 

[MAP]

Are you saying the break between each section is ALWAYS multiple spaces? Is it a specific number of spaces, or various?
Are the OTHER spaces (like between Account transfer), are those always just single spaces ?

from my limited observation, it seems the spaces between DATE and TYPE and DESCRIPTION and the dollar values are consistent - 4 or 5 spaces.

 

[Jonmo1]

This is where I was trying to go with my questions.

You have the right idea, though... all the OP needs to do is use Excel's Replace dialog box to replace double spaces with some unique character, then still using the Replace dialog box replace that unique character in combination with a space (so no leading spaces will remain) with the unique character by itself, then use Text To Columns specifying that unique character in the Other field and making sure the "Treat consecutive delimiters as one" checkbox is checked.

from my limited observation, it seems the spaces between DATE and TYPE and DESCRIPTION and the dollar values are consistent - 4 or 5 spaces.

The other question I had is more important here.
Are the OTHER Spaces only SINGLE spaces ?
Like the space between the words Account and Transfer.

 

[godsaaint]

You have the right idea, though... all the OP needs to do is use Excel's Replace dialog box to replace double spaces with some unique character, then still using the Replace dialog box replace that unique character in combination with a space (so no leading spaces will remain) with the unique character by itself, then use Text To Columns specifying that unique character in the Other field and making sure the "Treat consecutive delimiters as one" checkbox is checked.

Hadn't thought of that, nice trick/tip, thanks.

 

[MAP]

You have the right idea, though... all the OP needs to do is use Excel's Replace dialog box to replace double spaces with some unique character, then still using the Replace dialog box replace that unique character in combination with a space (so no leading spaces will remain) with the unique character by itself, then use Text To Columns specifying that unique character in the Other field and making sure the "Treat consecutive delimiters as one" checkbox is checked.

Mr. Rothstein, since I am going to "manually" place the text in the A column, I am seeking a formula to place in columns B, C, D, and E to extract the information from the A column. The way the text is 'downloaded' with the spaces, I know of no way to have Excel remove the spaces. But considering there is spaces

"04/21/2015______Account transfer______Transfer from CKH xxxx1234______$100.00______$1,100.00" (the underscores are actually spaces)

 

[MAP]

This is where I was trying to go with my questions.
The other question I had is more important here.
Are the OTHER Spaces only SINGLE spaces ?
Like the space between the words Account and Transfer.

Yes... there is only one space between the words within the TYPE and DESCRIPTION, but exactly 4 spaces between the categories.

 

[Jonmo1]

Ok, so like Rick Suggested,
Highlight your column of strings and go to Find and Replace (you can press CTRL + F)
Put 2 spaces in the Find What box
Put an _ in the Replace with box
Click replace all.


Now you can use Data - Text To Columns
Choose Deliminated
Check Other and put in an _
Check Treat consecutive delimiters as one.
Click Finish.