Capture Space within String

My function SplitName below correctly parses a last/firstname string with or 
without a comma between the name pair. The only problem I have is if the 
last/first name is passed to the function with no "space" after the comma. 
If you look at "TEST 2" below, you'll noticce that the first character "M" 
in the first name variable gets cut off when the space after the separating 
comma is not used.

Can someone help me modify my function so the function will correctly return 
the last and first name no matter if it's used with or without the leading 
space after the separating comma?


TEST 1: ****************

      Call SplitName("MyLastName, MyFirstName")

                   Results:
                                  Comma Found / Last Name: MyLastName
                                  Comma Found / First Name: MyFirstName

TEST 2: ****************

      Call SplitName("MyLastName,MyFirstName")

                   Results:
                                  Comma Found / Last Name: MyLastName
                                  Comma Found / First Name: yFirstName


CODE: *****************

Public Function SplitName(sName As String)

    Dim intComma As Integer, sFirst As String, sLast As String

    If IsNothing(sName) Then Exit Function
    ' Parse out first and last names

    intComma = InStr(sName, ",")

    If intComma = 0 Then
        sLast = sName
        Debug.Print "No Comma Found / Last Name: " & sLast
    Else
        sLast = Left(sName, intComma - 1)
        Debug.Print "Comma Found / Last Name: " & sLast
        sFirst = Mid(sName, intComma + 2)
        Debug.Print "Comma Found / First Name: " & sFirst

    End If

End Function


0
scott
6/6/2007 12:58:36 AM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
649 Views

Similar Articles

[PageSpeed] 46

On Tue, 5 Jun 2007 19:58:36 -0500, "scott" <sbailey@mileslumber.com> wrote:

>Can someone help me modify my function so the function will correctly return 
>the last and first name no matter if it's used with or without the leading 
>space after the separating comma?
>

Try using Trim() to remove the space, if there is one:

Public Function SplitName(sName As String)

    Dim intComma As Integer, sFirst As String, sLast As String

    If IsNothing(sName) Then Exit Function
    ' Parse out first and last names

    intComma = InStr(sName, ",")

    If intComma = 0 Then
        sLast = sName
        Debug.Print "No Comma Found / Last Name: " & sLast
    Else
        sLast = Left(sName, intComma - 1)
        Debug.Print "Comma Found / Last Name: " & sLast
        sFirst = Trim(Mid(sName, intComma + 1))
        Debug.Print "Comma Found / First Name: " & sFirst

    End If

End Function

             John W. Vinson [MVP]
0
John
6/6/2007 4:02:04 AM
Scott,

Try this...
sFirst = Trim(Mid(sName, intComma + 1))

-- 
Steve Schapel, Microsoft Access MVP

scott wrote:
> My function SplitName below correctly parses a last/firstname string with or 
> without a comma between the name pair. The only problem I have is if the 
> last/first name is passed to the function with no "space" after the comma. 
> If you look at "TEST 2" below, you'll noticce that the first character "M" 
> in the first name variable gets cut off when the space after the separating 
> comma is not used.
> 
> Can someone help me modify my function so the function will correctly return 
> the last and first name no matter if it's used with or without the leading 
> space after the separating comma?
> 
> 
> TEST 1: ****************
> 
>       Call SplitName("MyLastName, MyFirstName")
> 
>                    Results:
>                                   Comma Found / Last Name: MyLastName
>                                   Comma Found / First Name: MyFirstName
> 
> TEST 2: ****************
> 
>       Call SplitName("MyLastName,MyFirstName")
> 
>                    Results:
>                                   Comma Found / Last Name: MyLastName
>                                   Comma Found / First Name: yFirstName
> 
> 
> CODE: *****************
> 
> Public Function SplitName(sName As String)
> 
>     Dim intComma As Integer, sFirst As String, sLast As String
> 
>     If IsNothing(sName) Then Exit Function
>     ' Parse out first and last names
> 
>     intComma = InStr(sName, ",")
> 
>     If intComma = 0 Then
>         sLast = sName
>         Debug.Print "No Comma Found / Last Name: " & sLast
>     Else
>         sLast = Left(sName, intComma - 1)
>         Debug.Print "Comma Found / Last Name: " & sLast
>         sFirst = Mid(sName, intComma + 2)
>         Debug.Print "Comma Found / First Name: " & sFirst
> 
>     End If
> 
> End Function
> 
> 
0
Steve
6/6/2007 4:02:22 AM
thanks, i forgot about trim.


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:5ccc631h9qmgq4ukdf9j6862n6kmdtpf7b@4ax.com...
> On Tue, 5 Jun 2007 19:58:36 -0500, "scott" <sbailey@mileslumber.com> 
> wrote:
>
>>Can someone help me modify my function so the function will correctly 
>>return
>>the last and first name no matter if it's used with or without the leading
>>space after the separating comma?
>>
>
> Try using Trim() to remove the space, if there is one:
>
> Public Function SplitName(sName As String)
>
>    Dim intComma As Integer, sFirst As String, sLast As String
>
>    If IsNothing(sName) Then Exit Function
>    ' Parse out first and last names
>
>    intComma = InStr(sName, ",")
>
>    If intComma = 0 Then
>        sLast = sName
>        Debug.Print "No Comma Found / Last Name: " & sLast
>    Else
>        sLast = Left(sName, intComma - 1)
>        Debug.Print "Comma Found / Last Name: " & sLast
>        sFirst = Trim(Mid(sName, intComma + 1))
>        Debug.Print "Comma Found / First Name: " & sFirst
>
>    End If
>
> End Function
>
>             John W. Vinson [MVP] 


0
scott
6/6/2007 4:28:16 PM
Reply:

Similar Artilces:

email capture
I'm having a problem with Outlook not pulling the mail off my pop server. When I do a send and receive the mail is identified, "grabs" the messages like it's doing a download but when it reaches the end I'm getting an unknown error message. Any thoughts? 1) First, try to set up Outlook Express to download the email set. Sometimes it has better luck. 2) Another way is to clear the queue manually. This is technical, and not really recommended by anyone, but you may have a stuck message... Telnet to get it... (this method permanently deletes messages before you c...

Median of Even Set; How to Capture them?
Hi everyone, Say I have a dat set such as: 4 5 6 7 The median is (5+6)/2=5.5! However, I am not interested in the answer here, but the 5 and the 6!!! Is there an excel function or way to do so? Thanks alot, Mike Do you want the output in one single cell or in 2 separate cells? Mike Wrote: > Hi everyone, > > Say I have a dat set such as: > > 4 > 5 > 6 > 7 > > The median is (5+6)/2=5.5! > > However, I am not interested in the answer here, but the 5 and the > 6!!! > > Is there an excel function or way to do so? > > Thanks alot, ...

Capture user selection of font
If I put a font combo box on a Ribbonbar is there a way for me to capture the user's font choice? I need to capture that choice and use it in a macro. Whate *exactly* are you trying to achieve with your macro? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "dplaut"...

Visio 2003: Space shape could not be Auto Sized!!!
Hi There, I am using Visio 2003 with SP2. Following action returns error message every time on Visio 2003.. Please try it: 1- Open Visio 2003 and select "Building Plan" 2- Select "Home Plan" or "Floor Plan" from right pane. 3- Drag and Drop "Room" layout from Shapes on the left pane to the blank page. (Would be good, expanding room shape a little bit.) 4- Drag and Drop "Space" shape on the Room shape that you've already insterted. 5- Finally right click on "Space" shape and select "Auto Size". Bingo! You may see s...

Change Data Capture: Preserve capture instance data when adding co
When a new column is added to table that is configured for change data capture (cdc), the capture instance table will not have the new column until cdc is disabled and re-enabled for the source table. In the process the existing capture instance is dropped. I thought I could copy existing data out to a temp table and then copy back using the following SQL. However, other CDC meta information, such as the cdc.change_tables.start_lsn, becomes invalid. How can the capture instance history be preserved, using the same capture instance name, if at all? Thanks, Rich /*...

Capturing date/time modified on a form?
I have a form where staff can go and add student information, I would like to be able to capture when and if the form has been modified. We are running reports on students with certain behaviours right now but we don't know how up-to-date the data is if we don't capture if it's been changed recently. I added TimeModified and DateModified to my main table and put the fields on my form that I need it to capture but I'm not sure how to actually get it to fill those fields once data has been modified, any help would be appreciated. Monique Duane OK, what I've done now...

Exporting CSV file to unicode .txt file
I am saving a CSV file (which originated in Reporting Services) as a Unicode text file. When I open the text file there are " quotation marks around a text string. Is there a way to to Save As... without having the "" around the text?! Thanks! Paul I've never used Unicode characters, but.... Maybe you could use a macro that writes your data: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/text_write_program.htm) Chip Pearson's: http://www.cpearson.com/excel/imptext.htm J.E. McGimpsey's: http:...

Capture signature
I have a Ingenico 6550 signature capture device. When i sign on the device, it didn't capture the name. The display and the card capture worked, only the signature part doesn't work. I set the device name and everything, it loaded the form, and the tab for the signature on the pos poped up. I use the form designer to test the capture device it worked. The signature show on the form designer. I don't know what could go wrong. Could anyone help!!! By the way i am using RMS 1.3. "TJW" wrote: > I have a Ingenico 6550 signature capture device. When i sign on...

parse string with xml contents
Hi I have a string with the following contents: "<TR align=right ID=G><TD align=left><A HREF="/comun/fichaemp/fichavalor.asp?isin=ES0111845014"><IMG SRC="/images/arr-up9.gif" BORDER=0> ABERTIS SE.A</A></TD><TD>19,96</TD><TD ID=V>0,15</TD><TD>19,96</TD><TD>19,83</TD><TD>1.060.525</TD><TD>21.115,00</TD><TD align=center>27/06/2005</TD><TD align=center>17:35</TD></TR>" What would be the simplest way to parse it, and keep only &qu...

Delete Spaces
I've got spaces after data that's not supposed to be there, so VLOOKU isn't working because it doesn't see the data as being the same. Ho do I delete these spaces in one mass change? I've got 20,000 rows o data, I can't do it one by one -- Message posted from http://www.ExcelForum.com Change the VLOOKUP from =VLOOKUP(cellref,...) to =VLOOKUP(TRIM(cellref),...) >-----Original Message----- >I've got spaces after data that's not supposed to be there, so VLOOKUP >isn't working because it doesn't see the data as being the same. How >do ...

Show a named range within a label (not VBA)
I have a sheet that has a column value which states "Expenses in working currency". I would like to have it say "Expenses in USD" or "Expenses in MXP" (whatever the currency is). I have a named range in another sheet, ProjectInformation, that holds the working currency value. Not in VBA, but in the sheet I need to say something like: ="Expenses in" & (ProjectInformation!C9) but this gives me an error. Help, please. Thanks. Rick If I understand your set up correctly, try it this way... ="Expenses in "&Proje...

Remove Spaces beginning Time entry
I am using "=TIME(HOUR(B11),MROUND(MINUTE(B11),I11),0)" for a time card, but if a space is entered we get #VALUE error. How can these spaces be filtered out. Input "06:00" or "06:00 am" works, but " 06:00" gives #VALUE error. Thanks Jack Have a look at TimeValue in help. "Jack Bible" <jbible@bellsouth.net> wrote in message news:42B44038.3DE13425@bellsouth.net... > I am using "=TIME(HOUR(B11),MROUND(MINUTE(B11),I11),0)" for > a time card, but if a space is entered we get #VALUE error. How can > these spaces be filt...

Captured Quantities not Captured
I have a client with multiple sites. At one site (RETURNS and it is a site, not the returns bucket in the other sites) when we create a stock count schedule and start it does NOT capture the quantities on hand unless we limit the range of item numbers during the Mass Add function. This issue is reproducable ONLY ON THIS SITE. Any ideas any one? -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users For help learning and better using Dynamics GP,... check out our books at http://www.AccoladePublications.com ...

Tab within a cell
Hi, I know how to enter a new line in a cell (alt + enter) Please tell me for pressing a tab within a cell. -- Thanks a lot, Hari India I don't believe that you can, directly or via VBA. -- HTH RP (remove nothere from the email address if mailing direct) "Hari Prasadh" <excel_hari@hotmail.com> wrote in message news:%23LoZpueAFHA.1404@TK2MSFTNGP11.phx.gbl... > Hi, > > I know how to enter a new line in a cell (alt + enter) > > Please tell me for pressing a tab within a cell. > > -- > Thanks a lot, > Hari > India > > Hi Bob...

signature Capture?
Does RMS 1.2, 1.3, 2.0 support signatire capture pinpads? Which ones? Thanks. Sacha ...

automatic spacing between paragraphs
Please help. How do I turn off (for all docs) automatic spacing between paragraphs. Thanks Kevin Apply a paragraph style that doesn't have the spacing. In Word 2007, you can change the style set for the Word 2003 style set, which does not space the Normal style. (Change Styles from the Home tab). -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <&g...

Too much space before and after Tables Publisher 2003
Hello All, This is regarding MS Publisher 2003, with the latest updates all installed. I am having a problem making the text before and after a table appear right along the edge of a table. The tables are set to Exact, with 0 text box margins and Layout|Distance from Text set to all zeros. Still, when I move the table between paragraphs I often get too much space between the table and the next text line. Paragraphs are set to zero points before and after, too. This behavior has led me to go back to formatting documents in Word, where I don't have this problem with tables. However, ...

Uneven spacing between x-values
Hi, Hi, I am trying to construct a line graph in Excel, with the following data points: (30.00,2.72); (38.62,3.44); (40.23,3.58); (41.89,3.72). The line that intersects all these points should have the same gradient. However, when I input these values into the line graph in Excel, it is showing differing gradients, due to the fact that it is displaying the same "virtual distance" between the x-coordinates of 30 and 38.62 as it is between 38.62 and 40.23. WHy is it doing this? Many Thanks, Gary. Hi Gary, Try changing thechart type to xy-scatter instead of the Line chart...

space in word
i want the space between word, i used the following VB Code, But its not work. When i run the VB code, i get word seperated from number, but there is no space between word. what i have to make changes in VB Code. I am really thankful to Gord Dibben MS Excel MVP. again help expected Sub RemoveNumbers() ' Remove alpha characters from a string. ' except for decimal points and hyphens. Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR I...

Add second string of data to existing chart
Excel 2007 I have a chart with one line of data, about 7 points. The source data is in c7:k7. I want to add a second line of data with source c8:k8. When I do, it creates a second line as an sum total of point 1 (rows 7 and 8). I am using chart tools, design, select data. I have used the interactive guide with no success. Hi, Sounds like the chart type you have is Stacked Line rather than a normal line chart. So you need to change the chart type. Chart Tools > Design . Change Chart Type. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Lori&quo...

$50.00
Yes, $50.00 to the first/best answer to my problem. I have an MFC app and would like to capture the mouse outside of the application area. This application is used to capture the screen given specific coordinates. Currently I type the values in an edit box but would like to select the area using the mouse. The problem is the mouse can't be captured outside of the client area. So my idea is to create a window the size of the screen, either transparent or I will take a full screen shot and paint it with that. This would look like the screen and then the user could select the are...

capture the screen bitmap
I want capture the screen bitmap.use the code below,it is slow,use the 0.6 second.How can i get good idea to do this? CRect rc; HWND hDesktopWnd= ::GetDesktopWindow(); ::GetWindowRect(hDesktopWnd, &rc); int width=rc.Width(); int height=rc.Height(); HDC hdc = ::GetDC(hDesktopWnd); HBITMAP hBmp = CreateCompatibleBitmap(hdc, width,height); HBITMAP holdBmp=(HBITMAP)::SelectObject (MemoDC,hBmp); BitBlt(MemoDC, 0 , 0,width, height, hdc, 0, 0, SRCCOPY); LPBITMAPINFO pbmi; pbmi->bmiHeader.biWidth =width; pbmi->bmiHeader.biHeight =height; // GetDIB...

Segment Size in String Tables
What's the ideal size for a segment in a string table? In other words, are you better off filling a segment up with 16 LOOSELY related strings, or filling it with 3 CLOSELY related strings? Basically what I like to know is, does a segment with 1 string use the same amount of memory as one with 16 strings? Are you wasting memory if a segment only has 1 string? Ignore the whole problem. It is meaningless. Even the fact that you are asking a question as silly as "does a segment with 1 string use the same amount of memory as one with 16 strings" presumes that this matters i...

Delete hidden spaces in cells
Hello, I've got Excel 2002 SP3. I've got a big spreadsheet of names and addresses that I use as a data source for a mail merge in Word. If I select a cell in the city column in Excel and put the cursor up at the top where you can type or edit the cell contents, the cursor is not immediately to the right of the last character, rather it is a few spaces over to the right as if after the last character in the city name someone hit the spacebar a few times. For example: Instead of seeing Fesno| I see Fresno | This causes a problem when I do the mail merge because the label...

allowing email attachment within group only
hello, can anyone tell how to restrict emails to have attachment, sent within same email group, only? i'm using Exchange 2003. any help would be appreciated. Best regards, Farooq Khan See if this gets you going in the right direction: http://support.microsoft.com/default.aspx?scid=kb;en-us;827616 Tony Eversole "Farooq Khan" wrote: > hello, > can anyone tell how to restrict emails to have attachment, sent within same > email group, only? i'm using Exchange 2003. > any help would be appreciated. > > Best regards, > Farooq Khan > > > ...