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

squidmark

Board Regular
Joined
Aug 1, 2007
Messages
105
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

1. Data>TextToColumns>Space delimited

or

2. Formula:

Excel Workbook
ABCDEFGHIJ
1original DataElement1Element2Element3Element4Element5Element6Element7Element8Element9
290341* Aug 22 100.00 91512* Aug 06 456.3790341*Aug22100.0091512*Aug06456.37
Sheet1


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

squidmark

Board Regular
Joined
Aug 1, 2007
Messages
105
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top