SOLVED
Home

How to split string into 3 columns, using first space and last space characters found.

%3CLINGO-SUB%20id%3D%22lingo-sub-243444%22%20slang%3D%22en-US%22%3EHow%20to%20split%20string%20into%203%20columns%2C%20using%20first%20space%20and%20last%20space%20characters%20found.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-243444%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20transactions%20like%20so%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E12%2F05%20DESCRIPTION%20OF%20TRANSACTION%20HERE%2012345678%2025.00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20split%20them%20up%26nbsp%3Binto%20separate%20columns%20using%20the%20first%20space%20after%20the%20date%2C%20and%20the%20last%20space%20before%20the%20amount%2C%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDATE%20%7C%20DESCRIPTION%20%7C%20AMOUNT%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20is%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-243444%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Esplit%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-246160%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20split%20string%20into%203%20columns%2C%20using%20first%20space%20and%20last%20space%20characters%20found.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-246160%22%20slang%3D%22en-US%22%3E%3CP%3EMs.%20LeBow%3C%2FP%3E%3CP%3Ewelcome.%20Glad%20to%20help.%3C%2FP%3E%3CP%3EThe%20Date%20and%20the%20Amount%20is%20quite%20easy%20(I%20searched%20the%20net%20-%20as%20I%20always%20do)%2C%20the%20middle%20portion%20stymied%20me%20a%20bit%20so%20I%20had%20to%20get%20help%20from%20somewhere%20and%20Mr.%20Rothstein%20was%20very%20helpful%20with%20that.%3C%2FP%3E%3CP%3EThe%20amount%20column%20was%20formatted%20but%20it%20may%20not%20be%20what%20you%20need%20-%20you%20can%20use%20the%20column%20next%20to%20it%20and%20multiply%20all%20amount%20cells%20by%201%20to%20get%20the%20characteristics%20of%20a%20numeral%20then%20hide%20the%20D%20column.%3C%2FP%3E%3CP%3Ethanks..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-245635%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20split%20string%20into%203%20columns%2C%20using%20first%20space%20and%20last%20space%20characters%20found.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-245635%22%20slang%3D%22en-US%22%3EPerfect!%20Thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-244386%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20split%20string%20into%203%20columns%2C%20using%20first%20space%20and%20last%20space%20characters%20found.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-244386%22%20slang%3D%22en-US%22%3E%3CP%3EMs.%20LeBow%3C%2FP%3E%3CP%3Esorry%20for%20the%20on%20and%20off.%3C%2FP%3E%3CP%3EI%20think%20this%20time%20-%20its%20final.%3C%2FP%3E%3CP%3Epls%20see%20attached%20file.%3C%2FP%3E%3CP%3Ehope%20this%20helps%20-%20finally.%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-244383%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20split%20string%20into%203%20columns%2C%20using%20first%20space%20and%20last%20space%20characters%20found.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-244383%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Cindy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20try%20Flash%20Fill.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-243869%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20split%20string%20into%203%20columns%2C%20using%20first%20space%20and%20last%20space%20characters%20found.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-243869%22%20slang%3D%22en-US%22%3Eif%20you%20want%20to%20learn%20further%20-%20here%20is%20the%20site%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2016%2F06%2F01%2Fsplit-text-string-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2016%2F06%2F01%2Fsplit-text-string-excel%2F%3C%2FA%3E%3C%2FLINGO-BODY%3E
Highlighted
Cindy LeBow
New Contributor

I have a list of transactions like so:

 

12/05 DESCRIPTION OF TRANSACTION HERE 12345678 25.00

 

I need to split them up into separate columns using the first space after the date, and the last space before the amount, like this:

 

DATE | DESCRIPTION | AMOUNT

 

Help is much appreciated.

5 Replies
Highlighted
Highlighted
Solution

Ms. LeBow

sorry for the on and off.

I think this time - its final.

pls see attached file.

hope this helps - finally.

thanks

Highlighted
Highlighted

Ms. LeBow

welcome. Glad to help.

The Date and the Amount is quite easy (I searched the net - as I always do), the middle portion stymied me a bit so I had to get help from somewhere and Mr. Rothstein was very helpful with that.

The amount column was formatted but it may not be what you need - you can use the column next to it and multiply all amount cells by 1 to get the characteristics of a numeral then hide the D column.

thanks..

Related Conversations
AD User Migration Exchange Mailbox last
Hermulus in Exchange on
1 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Taking values from certain rows based on data input
Drakerla in Excel on
5 Replies
Add Column Using Count
Tim Hunter in SQL Server on
0 Replies
reverse text to columns for .csv import
milly87 in Excel on
3 Replies