find text between 2nd & 3rd "spaces", etc

[squidmark]

Hi all.
I've got a bank statement listing of cleared checks. But two columns of checks with the check number, date and amount all show up in one cell. for example, cell A1 is:

90341* Aug 22 100.00 91512* Aug 06 456.37

I'd like to take 8 columns and have the first column show what is before the first space (the first check# - 90341*, in this case), then the second column show what is between the first and 2nd spaces, etc.

I've got the '90341*' and "Aug 22" seperated into two columns using a =Find("Aug") function and pulling the data a certain number of characters to the left or right of the text place returned by the Find(Aug) function. But I run into problems when the dollar amounts are varying character lengths. So it just dawned on me that the geniuses at this board could probably help me just show what is between the spaces in seperate columns.

thanks.

 

[Andrew Poulsom]

You could use the SUBSTITUTE function to replace the first occurrence of Aug, eg:

=SUBSTITUTE(A1,"Aug","ZZZ",1)

and use that in place of the reference to A1 in your FIND formula.

 

[Richard Schollar]

Hi

1. Data>TextToColumns>Space delimited

or

2. Formula:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	original Data	Element1	Element2	Element3	Element4	Element5	Element6	Element7	Element8	Element9
2	90341* Aug 22 100.00 91512* Aug 06 456.37	90341*	Aug	22	100.00	91512*	Aug	06	456.37
Sheet1

Formula in B2 is copied to the right as far as required.

 

[squidmark]

Oh_My_Gosh! Space delimited.

Thank you for reminding me that the simplest answer is often the best. I love this board. Thanks for all the responses, but reminding me that all I have to do is re-import it had me kicking my own rear-end hard enough I have a sore back side.

thanks all.