Need to extract data mid-string.

Here's what I have:  \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754And here's what I'm trying to get back:  Swissco\Default.htmThe number of characters from the left will always be the same, 28.  And thestring will always be trimmed after the ":" (colon).  I'm struggling with how to use mid, left, right to get out what I need.  Thanks in advance!Kellie-- Message posted via http://www.accessmonster.com
0
KFox
3/26/2007 9:19:27 PM
access 16762 articles. 3 followers. Follow

4 Replies
1034 Views

Similar Articles

[PageSpeed] 43

On Mon, 26 Mar 2007 21:19:27 GMT, KFox via AccessMonster.com wrote:> Here's what I have:  \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754> And here's what I'm trying to get back:  Swissco\Default.htm> > The number of characters from the left will always be the same, 28.  And the> string will always be trimmed after the ":" (colon).  > > I'm struggling with how to use mid, left, right to get out what I need.  > > Thanks in advance!> KellieDo it in 2 steps in a user defined function.=Mid("\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754",29) Willreturn"Swissco\Default.htm: 754"Then Left("Swissco\Default.htm: 754",InStr("Swissco\Default.htm:754",":")-1) Will return"Swissco\Default.htm"In a Module,:Function ShortenString(StringIn as String) as StringDim strNew as stringstrNew = Mid(StringIn,29)strNew = Left(strNew,InSgtr(strNew,":")-1)ShortenString = strNewEnd Function==========Then call it from a query:NewColumn:ShortenString([FieldName])or in a report or form:= ShortenString([FieldName])-- FredPlease respond only to this newsgroup.I do not reply to personal e-mail
0
fredg
3/26/2007 9:49:23 PM
Wow!  Thank you!  I'd like to do the user defined function, but neglected to mention that thetext "\\lavaur4\PHIWEBEurope\SWAN" is different in each record.  The onlything that's constant is it's always 28 characters long.  For example:\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754\\labaoo3\PHIWEBNASbb\WIRT_Match\Default.htm: 766So, from those two records, I need to return:Swissco\Default.htmMatch\Default.htmKelliefredg wrote:>> Here's what I have:  \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754>> And here's what I'm trying to get back:  Swissco\Default.htm>[quoted text clipped - 6 lines]>> Thanks in advance!>> Kellie>>Do it in 2 steps in a user defined function.>>=Mid("\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754",29) Will>return>"Swissco\Default.htm: 754">>Then >Left("Swissco\Default.htm: 754",InStr("Swissco\Default.htm:>754",":")-1) Will return>"Swissco\Default.htm">>In a Module,:>>Function ShortenString(StringIn as String) as String>Dim strNew as string>strNew = Mid(StringIn,29)>strNew = Left(strNew,InSgtr(strNew,":")-1)>>ShortenString = strNew>End Function>==========>>Then call it from a query:>NewColumn:ShortenString([FieldName])>>or in a report or form:>= ShortenString([FieldName])>-- Message posted via http://www.accessmonster.com
0
KFox
3/27/2007 12:52:02 PM
Actually, I figured it out.  This is what I used:Step1: Mid([FieldName],InStr([FieldName],"_")+1,InStr([FieldName],":")-InStr([FieldName],"_")-1)Thanks again for your help!!KFox wrote:>Wow!  Thank you!  >>I'd like to do the user defined function, but neglected to mention that the>text "\\lavaur4\PHIWEBEurope\SWAN" is different in each record.  The only>thing that's constant is it's always 28 characters long.  >>For example:>\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754>\\labaoo3\PHIWEBNASbb\WIRT_Match\Default.htm: 766>>So, from those two records, I need to return:>Swissco\Default.htm>Match\Default.htm>>Kellie>>>> Here's what I have:  \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754>>> And here's what I'm trying to get back:  Swissco\Default.htm>[quoted text clipped - 29 lines]>>or in a report or form:>>= ShortenString([FieldName])-- Message posted via http://www.accessmonster.com
0
KFox
3/27/2007 1:11:54 PM
Thanks Ofer-- I'm going to write that down for future reference.  No doubt,this may come up again.  :)KellieOfer Cohen wrote:>Try>>Mid([FieldName],29,instr([FieldName],":")-29)>>> Here's what I have:  \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754>> And here's what I'm trying to get back:  Swissco\Default.htm>[quoted text clipped - 6 lines]>> Thanks in advance!>> Kellie-- Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access/200703/1
0
KFox
3/29/2007 1:08:00 PM
Reply:

Similar Artilces:

Entourage Database Needs Rebuilding Following Adding Contact from MobileMe or Inside Entourage
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Mac OS X: 10.6.2 <br> Entourage 2008 (12.2.3) <br><br>Hi, <br><br>I am posting this in the hope that I can get some advice on why I get prompted to re-build my Entourage database following me adding a contact to the MobileMe website? <br><br>I am a subscriber to the MobileMe service and since the 1st of Jan 2010, I have seen this strange behavior which is as follows; <br><br>1) I logon to the MobileMe website, create a Contact <br>&l...

How to assign #N/A N.A. data?
Hi, I need help, i just wanted a formula to assign "#N/A N.A." into "----", which is blank. thanks in advance. :) -- jolly79_ph ------------------------------------------------------------------------ jolly79_ph's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4835 View this thread: http://www.excelforum.com/showthread.php?threadid=499652 Can you post your formula ? You'd probably receive better, more specific responses that you could then apply direct into your sheet. Anyway, one usual way to error-trap #N/A errors is to use somethin...

String to xml document
Hi there, I was hoping someone can help me with a little problem I can't seem to find any answers to. I've got a form wich will be posted. On receiving the post I would like to parse a formvar to a xml document. The contents of this formvar (string) will be a valid xhtml document. Can someone give me a startertip (or more :-)) on how to achieve this? tnx in advance, James van der Veen Sjeems wrote: > I was hoping someone can help me with a little problem I can't seem to find > any answers to. > > I've got a form wich will be posted. On receiving the p...

VBA or Formula Needed ????
The vlookup table automatically updates when a the next destignate AF:AH orange background is updated. I now need help with a formula or VBA that will identify from thi table EC,ED,EE,EF; 1. which digit (0-9) in the EC column has the highest value in the E column and place that digit in the next orange row's W cell (for example W436). 2. which digit (0-9) in the EC column has the highest value in the E column and place that digit in the next orange row's X cell (for example X436). 3. which digit (0-9) in the EC column has the highest value in the E column and place that digit in...

reading textual data from CMemFile
Hello, I am trying to read textual data from a CMemFile, line by line. It works, but it is too slow. Is it possible to do it faster? Thanks for ideas Vaclav //return FALSE if we reached the end of the file //else return TRUE and fill the parameter with another line BOOL QCommonStorageReadMem::GetToken(CString &rString) { TCHAR ls_temp; rString.Empty(); while (m_memFile->Read(&ls_temp, 2)) { if (ls_temp == _T('\n')) { return TRUE; } else { rString += ls_temp; } } return FALSE; } You might want to try reading more than 2 bytes at a time and just...

Running formulas with data from mulitple workbooks
I need to create a summary report that uses various formulas (count, sum, datedif etc) that pull data from over 900 workbooks located in a single folder. All of the workbooks are formatted exactly the same with the same variables. I want this summary report to have each row represent one workbook with the columns being each new calculation that I run. I know nothing of macros and have been thrown into this. Perhaps giving an example with SUM formula may be a good place to start. This should get you started. Option Explicit Sub CreateSummaryWorkbook() Dim myFolder...

Importing data from xls
There is a routine that normally runs fine. It imports data from an .xls into Access. On one occasion the code would break, but I hadn't been able to track down the error. The import would fail. A user tried cutting the spreadsheet data from the original .xls, pasted it into a new .xls, ran the routine and the data imported just fine. This suggests that the the original .xls was corrupt in some manner. If that is the case, and if this were to happen again, is there any way I could trap the error? The original .xls file appears and acts just fine in all other regards. ...

backing up 2001 to CD and transfering data to another machine with 2002
I want to back up my money 2001 data on a CD-RW and copy it on another machine with money 2002. How do I do that? In microsoft.public.money, Ed wrote: >I want to back up my money 2001 data on a CD-RW and copy >it on another machine with money 2002. How do I do that? See FAQ available at http://www.bollar.org/msmoney/ for information. ...

formula needed to extract data from another file
Hi, is there any possibility to do the following: e.g: in file program.xls, cell A1 I enter: 4788 in c:\My Documents\data folder there are many files, also file 4788.xls I need a formula in program.xls, cell A2 which looks up the value of cell D5 in file 4788.xls Next time I will enter e.g. 3578 into A1 and the formula must then give back the entry of D5 in file 3578.xls Hope I made myself understandable. Regards, Norbert On May 15, 2:09=A0pm, Norbert <n.jae...@gmx.net> wrote: > Hi, > is there any possibility to do the following: > > e.g: in ...

data menu lost
Hi all The menu bar has changed from the standard menu functions to what appears to be chart funtions. I have file, edit, view, insert, format, tools, chart, window, help. I have lost the Data menu function and most of the functions under Insert and format. I have tried to reset the toolbar as well as uninstalling and reinstalling Excel with no luck. Thanks for advise Do you have a chart on that worksheet? And if yes, is that chart selected? (try clicking on a worksheet cell to get off the chart.) exile wrote: > > Hi all > > The menu bar has changed from the standard m...

duplicated data
I've a bunch of names and membership code of my fellow home makers...So how do I run a clean up of all the duplicated names? I really don't want to go blurry eyed trying to clean up manually THANX!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Take a look at data>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom "a_evie" <a_evie.v9on0@excelforum-nospam.com> wrote in message news:a_evie.v9on0@...

Data validation input message
Can anyone please offer any advice? ..... I am using data validation on a worksheet and would like to resize the message box that appears when the cell is selected. Can this be done in Excel either using an option or by using some VBA? Many thanks, DT. I'm not sure what message appears when the cell is *selected*. Are you referring to the error message that is triggered by an invalid entry? If so, AFAIK, there is no way to modify it. -- Vasant "DT" <dt0504@[cheatthespam]hotmail.com> wrote in message news:40e5e8d6$0$6441$cc9e4d1f@news-text.dial.pipex.com... > Can...

move row data to columns
We had a problem with some test data and instead of test results being recorded in multiple rows 5 columns wide, all of the data was recorded in one row multiple columns wide. The data consists of 5 readings taken every second for a period of 2 hours. Each reading is in it's own cell (no cell contains more than one reading). Do you know of a way that I can move each set of readings (5 test results) into it's own 5 column wide row? i.e. currently shows in single row as: 0, 0, 0, 0, 0, 128, 128, 128, 128, 128, 234, 234, 234, 234, 234, etc. and I need in 5 column wide rows: 0, 0, 0, ...

Consoldating Data
I have a spreadsheet with 57 columns of data. I'd like to figure out a quick way to have all the data (text) into just one column. I don't want add or multiply anything... just want all the data in 1 column. Hi Pierre You can use a function like this one Copy the function in a normal module =Rangecat(A1:BE1," ") Use this in the worksheet From J.E. McGimpsey It is working for rows an columns Public Function RangeCat(rng As Excel.Range, _ Optional delimiter As String = "", _ Optional direction As Integer = ...

Need a formula #4
I would like to have a formula that would calculate the following. Starting in year 2007 and ending in 2024 if each year the amount went up by 3 % each year My worksheet will have the years in column A and I would like the calculations to show up in column B Thank you to anyone willing to create this formula for me If you have the years in A2:A19 and in B2 you have the start amount, in B3 enter =B2*1.03 and copy down to B19 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "Just Me" <No@isp.com&...

Need HELP with Custom Dialog query
I seriously need some help. This problem has taken up way to much of my time. I've spent hours searching through example code and trying to change it so it works for me but nothing does. What I have is a small database where I need to allow users to search for repair shops. I have a 3 parameter query set up so the user can enter any or all of a shop name, address, and or zip code to pull up a shop. My problem is I would like to combine all 3 parameters onto 1 custom dialog box instead of 3. Could someone please write this query and code for me? The first time I opened Access was when I sta...

i need help retrieving an excel file from a floppy
I saved an excel file to a floppy that appears to be on the disk but cannot be retrieved. It may have been read only protected in its original location but does not appear to be read only now on the floppy. The error message indicates that it may be read only though. Help. Those 5 1/4" floppies can be tricky. Kidding, of course. Can you copy the file to your hard drive? It will be easier to work from the hard drive if you have to repair the file, for instance. ...

pop up says i need to connect to internet when downloading trial o
When I try to download the free trial of office pro, a pop up comes up on my computer that says I need to connect to the internet.I have been trying to do this all day, and I have had no luck changing settings, etc. Can anyone please tell me why this is doing this? I currently have the student version of office on my computer, I just recently bought it for a class I am taking this summer and found out that I need access also. I really need to be able to download this! ...

Need to create the character that means "with".C with a line over
Can anyone help me? I need to create the character that means "with". It is displayed as a c with a line over it. I have looked in symbols and windings and can not find the character. ...

Purchased another company: Need to quickly get addr books merged
I'm hoping that someone has had a similar problem and could provide advice on the quickets/easiest way to handle this. My company just purchased another. We currently have our own forest with 2 Exchange2k3 servers, the new company has their own forest with ~6 exchange 2k3 servers and 2 5.5 servers. Short term the goal is to get email interoperable between the two internally (i.e not traversing the internet) along with addr book replication. Any suggestions as to how to accomplish this goal. This will be my first time trying to attach 2 forests to each other and then having exchange pull...

How do I sort data in a sharepoint table?
I have a list in sharepoint that I want to sort by date. The info I insert is not inserted by date. Its randomly inserted and I want to later sort it by date. Is it possible to sort and save the data that way. I can click on the date colum and hit assending but it always returns to the version not sorted by date. Please help this is driving me mad! Thanks -- Jean Have you tried creating a view? In the specification for the view, you can set the sort field(s). --rms www.rmschneider.com Jean wrote: > I have a list in sharepoint that I want to sort by ...

Is anything needed if I only load Word & Excel from Office XP Sta.
I totally crashed my hard drive trying to download XP Svc Pk 2. It was NOT oriented for beginners like me! I ended up spending big $$$ getting my PC to work lost all my old data. Now I'm TERRIFIED at trying to download ANYTHING from MS! If I've ONLY downloaded MS Word & Excel from Office XP Standard is it really necessary for me to download all that stuff (or any of it) on the Office homepage? ...

How do I combine tabulated data into a single column and list alphabetically?
Hi, I have tabulated (grid) text strings (names) in cells in various columns and rows with the occasional unavoidable blank cell in the grid. How do create a single column of data (text strings) listed alphabetically? The icing on the cake would be to eliminate any gaps created by the original grid such that the column is unbroken. Any guidance would be much appreciated. Thanks Kev Nurse To eliminate the empty cells. select the column (one at a time) edit|goto|special|blanks rightclick on one of the select blanks choose delete shift cells up. (repeat for each column) === Start a new ...

Replace Data In A Table
I'm using sql 2000 and Access 2003. I have a table contains all the payroll data and a table that contains current payroll data. Each paydate I need to replace the data in the current payroll table with the current payroll data. In Access I could run a Make Table query that would do this. How can I do this with sql and Access? Thanks for the help, Paul Continue to use Access if your SQL programming skills are at issue. Use an Append query to add the data to the end of the existing table. "pjscott" wrote: > I'm using sql 2000 and Access 2003. > > I have...

How do I color specific data series based on location on data she
I have about 150 data series in a chart and I want to color e.g. 10 of them red based on where in the data sheet they are located, e.g. data i column J to T. Is there an easy way to do this? Format one of them the way you want. Select the other series you want to change and press F4. This may be difficult with 150 series, however. "Havard" <Havard@discussions.microsoft.com> wrote in message news:8F62297E-447B-4507-8D4A-A2C9A38C08EA@microsoft.com... > I have about 150 data series in a chart and I want to color e.g. 10 of them > red based on where in the data sheet ...