help with string functions please

Can anyone help with a formuila for the following, I'm struggling .

I have a large number of text strings (in a column), many (not all) of which 
contain 2 words or phrases separated by a colon, eg.   ABC : DEF  . I want 
to split the string into two at the colon, giving 2 columns one with ABC and 
the other with DEF. The colon itself is redundant. I need to be careful not 
to corrupt those text strings which do not contain a colon, leaving   the 
2nd olumn blank in such cases.

This seems messy to me, any help would be appreciated

Thanks

K 

0
trebor (88)
6/29/2009 11:15:27 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
711 Views

Similar Articles

[PageSpeed] 19

On Mon, 29 Jun 2009 12:15:27 +0100, "KRK"
<trebor@yeleek.nospam.freeserve.co.uk> wrote:

>
>Can anyone help with a formuila for the following, I'm struggling .
>
>I have a large number of text strings (in a column), many (not all) of which 
>contain 2 words or phrases separated by a colon, eg.   ABC : DEF  . I want 
>to split the string into two at the colon, giving 2 columns one with ABC and 
>the other with DEF. The colon itself is redundant. I need to be careful not 
>to corrupt those text strings which do not contain a colon, leaving   the 
>2nd olumn blank in such cases.
>
>This seems messy to me, any help would be appreciated
>
>Thanks
>
>K 



Look for "Text to columns" in your help file.
Assuming that there are no other colons than these separators  in your
text it will be very easy to split the text into columns.
Use "delimited" and use colon as the delimiter.

Hope this helps / Lars-�ke
0
larske6807 (34)
6/29/2009 11:36:51 AM
On Mon, 29 Jun 2009 12:15:27 +0100, "KRK"
<trebor@yeleek.nospam.freeserve.co.uk> wrote:

>Can anyone help with a formuila for the following, I'm struggling .
>
>I have a large number of text strings (in a column), many (not all) of which 
>contain 2 words or phrases separated by a colon, eg.   ABC : DEF  . I want 
>to split the string into two at the colon, giving 2 columns one with ABC and 
>the other with DEF. The colon itself is redundant. I need to be careful not 
>to corrupt those text strings which do not contain a colon, leaving   the 
>2nd olumn blank in such cases.
>
>This seems messy to me, any help would be appreciated
>
>Thanks
>
>K 

Select your column

Then select the Data/Text to Columns wizard
	Delimited
	: as separator (you may have to type this into the Custom Box
--ron
0
ronrosenfeld (3122)
6/29/2009 11:46:41 AM
Thanks,

 this works nicely, and is rather simpler than the formula I was trying to 
develop, which had Find, Iserror, Len, Left, Right functions embedded and 
which was not working.

Thanks again

K

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:obah451srmq7btan4o9p2kpt305m9dlrrd@4ax.com...
> On Mon, 29 Jun 2009 12:15:27 +0100, "KRK"
> <trebor@yeleek.nospam.freeserve.co.uk> wrote:
>
>>Can anyone help with a formuila for the following, I'm struggling .
>>
>>I have a large number of text strings (in a column), many (not all) of 
>>which
>>contain 2 words or phrases separated by a colon, eg.   ABC : DEF  . I want
>>to split the string into two at the colon, giving 2 columns one with ABC 
>>and
>>the other with DEF. The colon itself is redundant. I need to be careful 
>>not
>>to corrupt those text strings which do not contain a colon, leaving   the
>>2nd olumn blank in such cases.
>>
>>This seems messy to me, any help would be appreciated
>>
>>Thanks
>>
>>K
>
> Select your column
>
> Then select the Data/Text to Columns wizard
> Delimited
> : as separator (you may have to type this into the Custom Box
> --ron 

0
trebor (88)
6/29/2009 12:16:35 PM
On Mon, 29 Jun 2009 13:16:35 +0100, "KRK"
<trebor@yeleek.nospam.freeserve.co.uk> wrote:

>Thanks,
>
> this works nicely, and is rather simpler than the formula I was trying to 
>develop, which had Find, Iserror, Len, Left, Right functions embedded and 
>which was not working.
>
>Thanks again

You're welcome.  Glad to help.  Thanks for the feedback.
--ron
0
ronrosenfeld (3122)
6/29/2009 12:55:21 PM
Reply:

Similar Artilces:

Please help with date insertion
Not very good with excel so hope you can help I want to have a table with the date in column A a figure in column B wihich calculates a figure in colomn C Have worked out the formula for column B & C and a graph drawn for these figures. What I want to know is 2 things 1. how can I automatically have the date inserted in Column A when I enter this No? 2. How can I have a graph that only takes the sample data from colummn B against C for the last 7 days, constantly updating itself evry day that a new figure is entered Hope I have made myself clear dates from say 1st-31st of each month in co...

Criteria search function
Okay, I have a bit of a complicated task...let's see if I can write it out clearly. I have a report with many model numbers that I receive quarterly from a third party. Our internal database has a reference to the model numbers with corresponding part numbers associated with them. I would like to edit the third party report to search for the model numbers on my list and return the corresponding part number that goes with it. Tricky thing is, they're not identical in identifying the part. For example: Third Party Report: Model # = ABC-15SP2/DA Internal Database: Column A ...

Please help with Userform code. Thanks!
I have a userform that pulls data from a hidden sheet based on a combo box choice. I=92ll like to have a code to pick a number from the combo box, change a data in one of about 100 or so textboxes, and be able to click =93update=94 and the data is updated in the hidden sheet. I currently have the code below. The code in between =93--------------------code not working------------------=93 are not working, while the rest of the code works fine. This is just the beginning of writing this code. As you can see the code does not include all (about 100 textboxes/comboboxes/ checkboxes) that I need ...

Help needed with adding a record in a subform
I have created a stock control database and have a form with which to view/add/amend/delete single stock items. On another tab of the form, I have a subform which displays an audit table. What I am trying to do is create an entry in the audit table every time the stock record is updated, bearing in mind that several changes may be made to the stock item without leaving the form. What is the best way to do this? See: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips ...

Help with Advanced Budget
I am currently trying to setup an advanced budget. I go thru all the motions in setting up my paycheck and all of the categories in which I am budgeting for and the amount for which I am allocating. The problem I have is wheni go over to the bills tab and enter a bill, the amount on that particular category in the budget goes up throwing the budget off. I have tried everything i know and nothing is working. Can anyone help me with this please? Here's the deal: AB by design and code makes the assumption that everything in Bills is part of your budget. You need to enter the bills...

Cannot use Help Files on Excel 2002
When using Visual Basic Editor and trying to use Help on any topic Excel displays an empty box instead of the help file. I dont know if it is a Java client problem. I'd tried detect & repair and uninstalling the application but when re-installed it still doesn't work. please Help me use Help. Thank You Hi Jorge The VBA help is not automatically installed. Run your setup again and place a check mark next to it -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Jorge Zambrana" <jzambrana@bppr.com> wrote in message news:0eb001c3a2...

Formula Help #26
How do I enter a formula that counts based on two different criteria. I column A I have names and in column B I have dates or blank cells. need a formula that counts if there is a date in a specified cell i column B. The formula I have now is =SUMPRODUCT(A1:A12="Scratch")*(B1:B12="?????")) What would I put in the ????? to make it count if the cell has a date -- Message posted from http://www.ExcelForum.com The only thing I can think of would be a two step process. In C1, us the formula = if(and(isnumber(B1),A1="Scratch"),1,0), and so on dow the column. Th...

Date function within a query
Hello, I have a query that list individual projects, by project #, and the specific date each was started. I need to add a criteria or field that will only return projects that were started 180 days or 6 months ago. Is there a function that will look at the current minus the start date > 180 days? Below is the SQL: SELECT [tbl Project Detail].[Project Number], [tbl Project Detail].Description, [tbl Project Detail].[Current Month Spending], [tbl Project Detail].[NVL Total CM Budget], [tbl Project Detail].[Total YTD Spending], [tbl Project Detail].[Total TY Budget], [tbl Project Deta...

Reconfigured tab key help
I have a user who performed some unknown key combination and reconfigured her tab key function. Instead of moving 1 cell at a time to the left hers moves 1 screen to the left. I have been unable to determine what combination she performed and can not return the tab key to the orginal state. If anyone knows what I would need to do to return her to her original state please let me know. Hi Try Tools / Options / Transition / Transition Navigation Keys check box. Andy. "Gary Dennis" <gdenni@acxiom.com> wrote in message news:1e4501c3ff95$ba3c4d70$a601280a@phx.gbl... > I...

help with sorting
I am trying to put a golf scramble together. I am going to assign one player to every team and want to sort the remaining players randomly t each team. I would like to have four players per team. Can I do thi with excel? Thanks, Dan -- Message posted from http://www.ExcelForum.com list the other players in a column (assume column A) In column B put in the formula =Rand() then drag fill down the column next to you names. Select the filled cells in columns A and B, then do Data=>Sort and sort on column B. This will scramble you list of names. Now pick them 3 at a time coming down ...

Help Please
High I have one report showing Patients data like "country"diagnosis,etc" the report will order records by country and there is a header for the country. at the buttom of the report i want to put a summary like this country No of Patients country 1 10 country 2 2 etc how can I do this , do I need to generate a subreport, and if so , where i should place it , in the report footer or in the details section. actully , I have tried putting a subreport in the rpt footer , but un...

Help please!
I have a sheet with 2 columns of email addresses set up in it. There is some overlap in the information held in the 2 columns (I.e. some email addresses appear in both columns), is there anyway I can get rid the overlap so the email address only appears once? Thanks, Simon --- Message posted from http://www.ExcelForum.com/ Hi have a look at http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique Frank > I have a sheet with 2 columns of email addresses set up in it. There > is some overlap in the information held in ...

Help with group formula criteria
Here is a sample list (mine are much larger w/many more fields) Name Amount Date bob 100 04-05-05 bob -50 05-04-05 bob -25 05-05-05 sarah 200 04-03-05 sarah -200 04-06-05 dave 300 04-02-05 dave -150 04-27-05 dave -150 05-18-05 I only care about the values for the groups(name would be group in this case) in which the net effect of all amounts IS NOT zero. I need all the data in every field when that is the case. I am willing to put it in a Access table if necessary, b...

Impersonation Error when searching the Help
Guys, I'm getting the following error when searching the CRM Help: Server Error in '/' Application. ---------------------------------------------------------------------------- ---- Impersonation failure. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: Impersonation failure. Source Error: An unhandled exception was generated during the execution of the current web request. Informati...

can anyone help me on directshow filters
can anyone help me on directshow filters Hi goru, >can anyone help me on directshow filters Yes, there are a lot of tutorials out there in the www and the MSDN Library with DirectX Reference can help you: http://www.google.de/search?num=30&hl=de&newwindow=1&safe=off&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=DirectShow+Filter+tutorial&spell=1 Download SDK with Reference: http://msdn2.microsoft.com/en-us/directx/default.aspx Direct Show 9.0: http://msdn2.microsoft.com/en-us/library/ms783323(VS.85).aspx DevMasters.NET: Great List of Engines and Tuto...

Help with formula #13
Need some help with a formula. Here is the calculation I need to do: For every $10 more, 1% more on the price For every $10 less, 1% less on the price Any help is appreciated. =INT(A1/10)*.1 $100.00 $1.00 $90.00 $0.90 $80.00 $0.80 $70.00 $0.70 $60.00 $0.60 $40.00 $0.40 $30.00 $0.30 $20.00 $0.20 $10.00 $0.10 >-----Original Message----- >Need some help with a formula. Here is the calculation I need to do: > >For every $10 more, 1% more on the price >For every $10 less, 1% less on the price > >Any help is appreciated. >. > That would be good if it was a simple...

Basic Question
I have started using Project since last week only. I have a project plan, but a resource has requested a days holiday. This is in the middle of a task. How can I account for this in the plan please? Ideally I want to avoid increasing the duration of the task but some how account for the fact that the resource didn't work on it for a day. Thanks Frankly, I would recommend you not worry about it. You've already computed the plan and have set baselines etc. Instead, ask the resourse to assure you that despite taking this unplanned holiday they still will complete the as...

Auto email response/Help me
Worksheet Number 2, has Column B6:B14 customer name, Column E6:E1 status "OK" or "Overdue", column F, F6:F14 has an email address... I want to create an automatic email. If E6:E14 states "Overdue" the send an email to that email address, to that particular customer.. Thanks Mar -- Message posted from http://www.ExcelForum.com Ron de Bruin has lots of sample code at: http://www.rondebruin.nl/ And Dick Kusleika has some (especially Outlook) at: http://dicks-clicks.com/excel/olAutomating.htm "Mark1ace1 <" wrote: > > Worksheet Number 2, ...

Does CRM have the same basic functions as GoldMine?
I am looking to change our systems, we currently use GoldMine . We want to start using mobile technology. Does CRM allow you to attach notes when completing an appointment and then report on all the contacts linked to a project displaying name, company, notes for a particular meeting. Example, I create a project called Senior Candidates, lets say there are 15 people attached to this project, I create a call back and then complete it on all the candidates. I then setup meetings to see 5 people and complete them. I add notes to all the appointments and calls. Can I print out a report to ...

need help with office picture
I edited a photo in Micorosoft Picture manager. I went to colour, enhance colour, clicked on my photo to enchance it. The photo turned really light in colour, which i dont like I also saved this photo after I enhanced it.How do I recover the original photo ? You dont, you overwrote it, unless you have a backup elsewhere "Leigh" <Leigh@discussions.microsoft.com> wrote in message news:C3F2B6E3-4A3C-45FA-BF07-9E067DFA72FA@microsoft.com... >I edited a photo in Micorosoft Picture manager. > I went to colour, enhance colour, clicked on my photo to enchance it. ...

Help, problem with set up Outlook 2003
I have Outlook 2003. When I was setting up Outlook with the wizard, initially I was setting up Outlook to use my office web exchange server. Then it said I needed to be on line. Then I found out that I would only be able to access Outlook when in my office, which I did not want. So then I wanted to cancel this type of set up & not use the server and only wanted to set up an email account. I am unable to change the set up. Outlook keeps trying to access online & connect to the Server ( which was never set up) when I try to cancel my computer freezes up. I don't kn...

Quick Help with Excel Formula #2
ok, I have a cell where i want to select one of 15 names, how do i click o the cell and a drop down menu appears with all the names, so then i ca select one and this appear in the cell? thank -- Message posted from http://www.ExcelForum.com Use data validation. >-----Original Message----- >ok, > >I have a cell where i want to select one of 15 names, how do i click on >the cell and a drop down menu appears with all the names, so then i can >select one and this appear in the cell? > >thanks > > >--- >Message posted from http://www.ExcelForum.com/ > &...

How can you put double quotes in the parameter of the INDIRECT() function ?
How can you put double quotes in the parameter of the INDIRECT() function ? Example .. =SUMIF(mysheet1!$B$2:$B$200,">0",mysheet2!F$2:F$200) I want to run the above formula in the INDIRECT function like . INDIRECT ("=SUMIF(mysheet1!$B$2:$B$200,">0",mysheet2!F$2:F$200)") But the " around the >0 is causing a problem because INDIRECT thinks you have come to the end of the literal parameter - but I haven't - I literally want the double quotes passed to INDIRECT as part of its parameter. "tur13o" <research@basingstoke.plu...

JET dB Newbie Needs Help
Hello all, I'm haveing a bit of a problem with writing to a jet dB and looking for a little help. The problem is that for some reason data is sudenly being written to the end of the new data which is appearing BEFORE the older data. It is as though JET does not recognize the fact that the older data even exists. Each time a record is added it writes it to the end of the more recent data and pushes down all the previously existing data. This is how I open the dB and recordset: (please excuse word wraping) Set eScalperDB = OpenDatabase("C:\Documents and Settings\My Databases\eScal...

Pls help me ! #2
Hi there! Good morning. I have a problem here, hope you guys and gils can helps me. Thanks! I need to show some details from a table (file attached stationary123.xls) using bar chart. so that I can see the number o different items (ex: book, knife or ruler) with 2 bars for types A & every month. Do you think msExcel can do that? Or you think I should put it in some others way or modify my table? Thank you very much. davidie -- Message posted from http://www.ExcelForum.com From what you have posted (no file attached though people are reluctan to download them), it sounds like a piv...