LinkBack URL
About LinkBacks
I need a function that split the content of the text strings below into four cells in each line. The functions shall of course be located in the four cells.
Please Login or Register to view this content.
I need a function that split the content of the text strings below into four cells in each line. The functions shall of course be located in the four cells.
Please Login or Register to view this content.
Last edited by mkvassh; 07-12-2010 at 10:11 AM.
Hi
could you please provide an example of the outcome you are looking for? Are ther spaces between each string?
Hi, try the 'text to columns' function - go to the 'Data; menu - then 'Text to Columns' you can select the cell with the data then choose the delimiter - I tried it using 'space' as a delimiter and it returns the data in separate columns - you'd then need to put the 'Accounts' and 'Payable' bit back together - but that is a fairly quick one!
remember though if you don't specify a new destination for the new columns, the divided data will replace the combined data.
See attachment.
I want to end up with the data in Column B - E based on data in column A. I'm aware of "text to columns", but that is not a good solution in this example since one have to adjust data afterwards. The plan is to use this on a lot of data.
The problem here is of course the extra space in one of the line. I think a solution must split from right.
Sorry, nothing attached - Edit your original post - Click " go advanced" and follow the attachment wizard
Sorry. It is there now
If it is only one or 2 words as per your example
then in B1
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=4,SUBSTITUTE(A1," ","^",1),A1)
dragged down
you can the copy paste special /values /back over this to remove formulas
then use text to columns space as delimiter
finaly find ^ replace " "
Last edited by martindwilson; 07-12-2010 at 08:24 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Your suggestion will work, thanks, but there is a lot back and forth. I know how one can do this in vba (see link below), but isn't there a way to do this by using a function without using several steps?
http://www.excelforum.com/excel-prog...xt-string.html
that takes aproximately 20 seconds to do!
I agree, but isn't there a way to do this one go?
Try this (attached)
Edit: to clarify, there are three different functions going across, but they should be draggable down, as long as the text in column B is the same length as the prefix text in column A.
Last edited by SpeedingLunatic; 07-12-2010 at 10:00 AM.
It's working. Thank you :-)
add in cell b1 of speedlunatics
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=4,LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",2))-1),LEFT(A1,FIND(" ",A1)-1))
I just want Formula
to Split text into 4 columns Plz help
If i enter a Full name eg: Mr Sachin Ramesh Tendulkar
in one cell then by using find control
i want dis cell splited and want each
word in 4 colums of cell plz provide a formula
on kiransolkar@gmail.com
thank You
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks