Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Separating First Name && Last Name After Comma

Hi everyone,

Does anyone have an idea of how I could separate a first name from a last name in a cell, that looks like this;

Smith, John in column A and I want to create a macro that will take the first name occuring after the (,) comma, and place it in Column B.

Any ideas would be greatly appreciated.


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Separating Fields Based On Comma
Hi Everyone,
I wanted to search a table containing multiple fields.
When the input for search is either a word or numeric value there are no issues.
Now I wanted to input multiple values separated by a comma.I want to separate the inputs at comma and then run a search loop.
Can someone tell me how to separate the input based on comma?

One Comma, And One Comma Period.

This statement has worked for me before, so I can't figure out why it doesn't work now.

I need to search a string to ensure that there is only one comma. So I have used this in the past:

If InStr(MyString, ",") = 1 Then
' Perform routine
End If

Now for some reason, it returns the value "8" which is the position of the comma in the string. Why is it not working anymore? Is there a better way? Thanks!

Separating Strings
Hello, I have a string (let's imagine) "Key 12"
How can I separate "key" or any other text from the 12 and put them both in diferent variables?

P.S: I have tried using the "INSTRREV" didn't work T_T

Separating Numbers
Is it possible to separate out the digits of a number?
EX: 73926 = 7, 3, 9, 2, 6
OR: -93714 = -9, 3, 7, 1, 4

Is this done like the Chr command?

Separating A Listbox

I have put two fields of one query in one listbox, separated with a - . Now I need only the first part. How do I get that part?


Separating One Field Into Two
I have a name column in one field in an Access table set up Smith, Jane I want to separate out the first and last name into 2 different columns via a query or vba. In the same database I need this accomplished

i.e., Name Last First
Smith, John -> Smith John

Separating Text In Db
I've got an app that stores names in an access db as a single field entry, "Name". It contains first and last names, in that order.

What I'd like to do is, somehow, create 2 new fields, firstname and lastname, loop through the Name field separating the two and placing them in the correct fields. I can't think of any way to do this, and have been searching the boards looking with no luck--

Any thoughts?


Separating The Word
i have a word at a string:

x = "book"

and i want to do this:

x1 = "b"
x2 = "o"
x3 = "o"
x4 = "k"

but the string changes and I don't know to do this because one time is 4 letters but other time is 5 letters.

any help??


Separating String
What would be the most efficient way to separate this kind of string: "Word1 Word2 Word3 Word4 Word5", and put each word to separate textbox?

Thanks in advance.

Visual Basic Programmer (at least I want to be one)
You will hear a lot about it.

Separating String
What would be the most efficient way to separate this kind of string: "Word1 Word2 Word3 Word4 Word5", and put each word to separate textbox?

Thanks in advance.

Visual Basic Programmer (at least I want to be one)
You will hear a lot about it.

Separating Data
Hi! I just want to ask if there is another way to separate data aside from using Split? Thank you for your help!

Separating Incoming UDP Data
I am working to make a client/server app combo but I need info on a couple things. There will be perhaps dozens of clients sending data to the server at any one time through UDP.
Since I know that UDP is connectionless, my main concern is that data coming from different clients is going to get mixed together.
I need to figure out how I can accept incoming data simultaneously from multiple sources while keeping all of the data separate.
Any help on this is much appreciated.

Possible? Separating PPT Slides That Have Animations
I want to take a PPT slide that, for instance, has 3 animation effects. I want to split the slide into 4 seperate slides...the first slide being the original slide with none of the animations (or objects that were being animated) on it. The next with the first of the objects on it, the next with the second of the objects, and the last with the third object. None of the animation needs to be preserved, but if it is, thats fine...I just need to end up with 4 static slides at the end. When watching the presentation, it will look the same...but when exporting a gigantic group of PNGs, it will retain those images as well. That is the goal. Any ideas?

Separating Value Of One Cell Into 2 Cells
Hi there. I got hundreds of user names with format of "surname, other name" (e.g. Goodman, Philip) stored in one column. Now I want to separate the surnames in one column and other names in another column for ease of maintenance. I guess there is a way to do that by identifying the "," but fail to kick it off in vba (excel).
Any hints please?


Separating Data For PicBox
earlier today I asked a question about separating data to print to picBox
with this data:

"Nike 606","10B",4,59.95
"Adidas 674","8.5C",3,63.75
"Puma 45","9A",6,74.25
"Brooks 30","8C",7,52.50

I came up with this:
Dim brandandmodel As String, brand As String, model As Single, sizeandwidth As String, size As Single
Dim width As String, qty As Integer, price As Single, pos As Integer, Length As Integer

Input #1, brandandmodel, sizeandwidth, qty, price
pos = InStr(brandandmodel, " ")
Length = Len(brandandmodel)
brand = Left(brandandmodel, pos - 1)
model = Right(brandandmodel, Len(brandandmodel) - pos)

width = Right(sizeandwidth, 1)
picBox.Print brand, model, size, width, qty, FormatCurrency(price)

and it is working - everything prints out perfectly but I am of course getting "0"'s in the size column----
I can't get the "size" out to put into the size column. Can someone help? I have tried every Instr and Len and Left and I am not getting it

Separating Out A Control Array
I need to validate a bunch of check boxes so that at least one is checked, but i dont know how to do that without it having to have all the boxes checked.

Dim x As Variant
For x = 0 To 7

If Trim(chkInterest(x)) = 0 Then
MsgBox "Please select an interest!", vbCritical, "Input Error"
IsValidData = False
Exit Function
End If

Separating Numbers From Letters
hello, i am working on a database program and i want to generate a referencenumber automatically.
i figured it would be best to look at the latest referencenumber and then simply add one.
the problem is that the ref. nr is made up out of letters and figures ( for example : trdbk 300/3021 )
so in order to add one i need to separate the last 4 figures of the reference number and then add one.
i have come up with this.

mkrefvalue = rs3.fields("reference_nr")
tradelast4 = right(mkrefvalue, 4)
referencevalue = int(tradelast4)
referencenrgen = accountvalue + 1
test = "trbk 300/" + str(referencenrgen)

the following variables are declared

: dim test as string
dim mkrefvalue as string
dim tradelast4 as string
dim referencevalue as integer
dim referencenrgen as integer

but this does not seem to work.. only works for the first nr generated, then the second time i get a type mismatch.

can anyone help?? thx.

Separating One Recordfield Into Two Variables?

probably easy question, but not for me...

I'm using ADO and a recordset to retrieve some data in MS Access via SQL. The problem is that one field 'CandidateName', which contain the first and last name of a person separated by a blank space, has to be separated into two separate variables FirstName and LastName. I know it is possible, but my knowledge doesn't reach that far anymore. And how do ensure that in the second variable the blank space isn't included?!

Can anyone help? Thx!

MIDI: Separating Channels
Hi, I'm pretty much new to programming, did a little basic a few years back, but I could really do with some help with this:

I need to take a MIDI file, and to 'select' channel 10, the drum track. Then, I need to have a program that will convert each event into a section of code that I can download to a PICAXE microcontroller. I do not need to play the MIDI file itself, only decode each event on channel 10 and turn it into a chunk of PICAXE code.

The reason behind this is that I'm building a robotic drummer. It's driven by pneumatic actuators that I am controlling via this PICAXE. What I am trying to achieve is to write a program which will take any MIDI and get my robotic drummer to play it!

Thanks in advance for any hints or tips!


By the way, it's in this section because a friend suggested I use API calls to do this, if that is wrong, please tell me what section it fits best!

Oh, and I have VB6.

Separating A String With A Variable
hi all,i don't know how to put this ....
suppose i get a string:-

*****this is what i want&#&#&#
where the the string inbetween ***** and &#&#&# is a variable i.e keeps on changing, how do i obtain the string inbetween ***** and &#&#&# and omit the rest

Separating And Formating Strings
I've been messing with Outlook here late (not very good at it either). And I've come accross something with the receive date and time that has befuddled me.

I'm trying to sort the email list by date and time and need a faster way of doing this rather than character searches of the string.

Anyone have a way of separation this date and time string into 2 separate strings then formating the date and time for a faster sort?

String from Outlook = "5/9/2007 10:10:43 AM". Need it broke into 2 parts; str1 = "5/9/2007" and str2 = "10:10:43 AM"

What I need for a proper sort; str1 = 05/09/2007 and date I think is gonna be ok. Once the 2 strings are broke, I can join them back up. Its just the time that is the savings I'm looking for.

Any help splitting the string and formating the date is greatly helpful.

Separating Menu Selections
I know this is an easy fix, but idk, how do you separate menu selections like in file in microsoft word.

Separating Option Buttons
I seem to be having trouble separating my option buttons. Two options for one thing, two for another, but only one will work at a time.

I put them in different frames thinking that would solve it, but to no avail.

I tried making them into two different sets of arrays with no luck.

Why it no work?

Separating Values In A Variable
I am using excel and have a cell where a user enters "\servernamesharenamefolder1

What I need to do is break up "servername", "sharename" and "folder1
unfolder" into 3 different variables in a sub routine. as a VB newbie, how would I do this?

Separating The Date From The Text...
Trying to query a SQLServer7.0 DB.  One column has data in the following format "text - Monday : 03-24-2003".  I need to select where this column is "like 03-24-2003" (minus the preceeding text).  (...where column like '%03-24-2003' works for a single date...but what code do I need to always look for today's date)?  In other words, how can I combine the preceeding % wildcard and date condition?  Thanks!!

Separating Last And Fist Name In The Same Field
I'm working in MSAccess. I'm trying to separate the Lastname,Firstname (Doe,John) and put the Last Name and First Name into their own separate field. How can I do this in a simple way?

Separating The Last Word In Each Cell In A Column
Is there anyway to separate off just the last word in each cell in a column. Some cells have two words and some have three of four, so I'm not sure if Text to Columns can do it. Thanks

Separating Data For PicBox Print
I have been at this for a few hours and I am not getting anywhere.
Here are the instructions for this:

3. read one record from the data file.
' 4. Take the data in the brandandmodel field that you read in and use the
' apppropriate functions to break the field apart. Place the brand in
' the brand variable. Place the model in the model variable
' 5. Do the same as in #4 for the sizeandwidth field that you read in. (i.e.
' Place the size in the size variable and the width in the width variable.
' 6. print out in the picturebox a line of information for this record.
Here's the data:
"Nike 606","10B",4,59.95
"Adidas 674","8.5C",3,63.75
"Puma 45","9A",6,74.25
"Brooks 30","8C",7,52.50

I am gtting line one but what is happening is that I am writing it for each line not according to instructions ( like this:Input #1, brandandmodel, sizeandwidth, qty, price
brand = Left(brandandmodel, 5)
model = Right(brandandmodel, 3)
size = Left(sizeandwidth, 2)
width = Right(sizeandwidth, 1)
picBox.Print brand, model, size, width, qty, FormatCurrency(price)

Can someone point me in the right direction?

Separating Columns In Text Files
Hello, heres a quick question. When I have a text files that will be used in a vb program the people I work with want to separate each column in the text file with a "|" instead of a "," that I always use. When I do this command:


Input #Fnum1, photo, Description, category, subcategory, station


Will it work if I do this:


Input #Fnum1| photo| Description| category| subcategory| station


Or can I keep it as teh first input command and still work. I would try it out but My visual basic software is not working right now. Thanks.

Separating The Lines In A Multiline Textbox
My textbox contains 4 lines. What I want to do is separate these lines into strings i.e.
str1 = Line1
str2 = Line2

Simple enough I know

Separating Text In A Txtbox Into Strings
I'm new to VB and have a project where the user types some basic info into a txt box(txtInput) and when he/she presses enter the txt parses itself into unique list boxes (lstFN, lstLN, lstWT, lstHT). I'm having issues with defining the breakup of the text. Specifically, when the user enters his first name, last name, height and weight in the text box the text doesn't format into the correct list boxes (lstFN, lstLN, lstWT, lstHT). You'll see that I have a declared variables named "space1", "space2", "space3" and "L" whose goal is to breakup the text into the correct list boxes. Any suggestions would be appreciated.

Option Explicit
'---dim stmts

Dim strFN As String
Dim strLN As String
Dim strHT As String
Dim strWT As String
Dim N As Integer
Dim space1 As Integer
Dim space2 As Integer
Dim space3 As Integer
Dim L As Integer
Dim curHTAvg As Currency
Dim curWTAvg As Currency

'---exit button
Private Sub cmdExit_Click()
End Sub

Private Sub ExtractStrings()
    strFN = Left(txtInput, space1 - 1)
    strLN = Mid(txtInput, space1 + 1, space1 + space2 + 1)
    strHT = Mid(txtInput, space1 + space1 + space2 + space2 + space3, space2 + 1)
    strWT = Right(txtInput, space3 + 1)
End Sub

'---loop code
Private Sub txtInput_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 And Len(txtInput) <> 0 Then
    For N = 1 To Len(txtInput)
    If Mid(txtInput, N, 1) = " " Then
    If space1 = 0 Then space1 = N
    ElseIf space2 = 0 Then space2 = N
    ElseIf space3 = 0 Then space3 = N
End If
'----extracts strings from txt box
'----puts strings into list box
    lstFN.AddItem strFN
    lstLN.AddItem strLN
    lstHT.AddItem strHT
    lstWT.AddItem strWT
'----clears txt box after keypress
    txtInput = ""
    space1 = 0
    space2 = 0
    space3 = 0
    L = 0
End If
End Sub

Separating Lines In A Text File
In my program i save text in a .txt File.

Its hard to view the data as it just goes in one continuos line.

How would i go about separating the lines when the text
has reached the sceens width (say 200 characters...)

Thanks for your help

Access Syntax Help.. Separating A String
I'm trying to separate this string by the first two chars and add them to columns. If i had a query that separated based on the first two chars with an "IIF" statement to handle anytime it sees an "XMG" string to get the 3 chars instead of two.

But the problem is, handling codes that are 3 chars long and not two.

2 chars = RDNRG105B-0900
3 chars = XMGPHN108-0050

This is an example of what i'm trying to do. I have a column of those strings, now I want to tokenize it. Any help would be greatly appreciated. Thanks, Essa
Code:Design Code    Prj. Code    DESIGN    REF    REVISION.    POWER    SPH    CYL    AXIS    ADD L/R

XMGAV2400-BC     XMG    AV2    400        BC                    
RDU2604Y-FC     RD    U    2604        FC                    
RDNRG105D-100     RD    NRG    105    D    PI    -100                
PDPHN101E-RC     PD    PHN    101    E    RC02                    
RDB3N105-1.00/+1.50    RD    B3N    105        -1.00            150        

Separating Name Field Into First Middle And Last Portions
Does anyone have a relatively easy way to separate a text field containing the full name of a person into First Middle and Last name portions?


Separating A Srting And Converting It To A Byte
I am sorry for this simply question but I can't get it to work.
basically, I have the following program:

Dim a() As Byte
Dim i As Integer
Dim msg As String

msg = "-831.40, 198.30, 467.00, -164.50, 93.30, -124.30,"

For i = 1 To Len(msg)
a(i) = Mid(msg, i, 1)
Next i

I want to separate each character into an array then convert the array to a byte.
1. is that possible?
2. I get an error "type mismatch" for the a(i) line

any ideas?


Separating Letters From A Word (bmp Image In Picture Box) For Script To Text
hey i am really stuck at this point...
.... in my project i am converting gujarati script to text. i am successful to do it for single letters taken as bmp image in picture box.
................ ""now i want to separate out letters from word (in a picture box) to different picture boxes ( for each letter) ""
i am not able to do this there any way or well formulated method which i can apply in VB 6???

please suggest some ways or algos!!!

Please Help Building Up SQLs For Separating Out Year, Month And Quarter Data From A Table
Hello Ppl..

I want help regarding generating different SQL statements.....for the following kind of table......

So what I want to do is to run queries to find out monthly, quarterly and yearly data separately.....

1) jan'05, feb'05, ..... jan'06, feb'06......are monthly data...

so what I want is (logically)

when month is selected on the form for the output data,
ssql = "Select s.*jan* AND s.*Feb* AND s.*Mar*...........& _
         "INTO [MONTH]" & _
         "FROM [" & Me.PCYcle & "] s "

but SQL does not support this kind of * statements. I am sure that month field will include Jan, Feb, Mar, that kind of string , but it can be for year 05, 06, not sure about the year part.

2) 05'Q1, 05'Q2, ..... 06'Q3, 06'Q4......are quarterly data...

so in this case,

when quarter is selected on the form for the output data,
ssql = "Select s.*Q*" & _
         "INTO [QUARTER]" & _
         "FROM [" & Me.PCYcle & "] s "

but SQL does not support this kind of * statements. I am sure that month field will include Q1, Q2, Q3, Q4 that kind of string , but it can be for year 05, 06, not sure about the year part.

3) 05'Y, 06'Y, 07'Y......are yearly data...

so in this case,

when year is selected on the form for the output data,
ssql = "Select s.*Y*" & _
         "INTO [Year]" & _
         "FROM [" & Me.PCYcle & "] s "

but SQL does not support this kind of * statements. I am sure that month field will include Y in all year data column, but it can be for year 05, 06, not sure about the year part.

Please help building up this kind of logic and SQL statements... I am not sure about the year part in columns, it can be can not specifically define columns like,

select s.05'Q1, s.06'Q2, s.07'Q3........

Because I am developing this application for all kind of input tables and they may have different year range, so need some kind of dynamic way to generate this.

Thanks in advance....

How To Add Comma
I have listbox. See the attach files.. I want to add comma and [ ] in the textbox
like this [code], [N], [name] when I click the command button. how to do that?

Chr I Need A Comma
i made a program to show all the chr code and meanings from 0 to 300. i found 2 command but none seem to work right when i save it to a text file and open in another editor.

Problems With Comma's
When grabbing a line from my text file, it stops at the comma. such as:
hello, how are you
returns as "hello", which of course is not what i want it to do, any ideas?,

Comma Delimiter?
I have a problem with reading comma in a text file. This is the code i use to read each line from a text file:

Dim fnum As Integer
fnum = FreeFile()
Open (sFile) For Input As #fnum

Do While Not EOF(fnum)
Input #fnum, MyString
'Listview.Add MyString
Close #fnum
The problem is that my text file contains commas and if my textfile contains "1,2,3,4" it will display:
in the listbox instead of 1,2,3,4.

Please help.

Convert Comma To DOT

I wants to write a Programm that can put a comma into a dot.

example: I wants that the number "123456789,254" would be show "123.456.789,254" and when I will Save it the number must be "123456789.254" for the Oracle Database.

So how can I convert the last comma to a dot? What must I do?

thx for help!

Comma Vs Point
Hi to all,

do somebody know how
"100 / 1, = 610000"
can be avoided?

It seems a problem of American/European standard (comma vs point)
I don't know how to have from
"100 / 1,639344229508" the result of 61,00000122

thank you

Add Comma In Number...
How would i add a comma in a number...I have this:

numberlen = len(number)

If numberlen = 5 then
'Add a comma after second number

End if
How would i add that comma?

Comma Input
This is probably a stupid question, with a stupid answer, but it's been bothering me for over 2 years now, and I still haven't found any solution yet, so here I go:

When I try to input data from a txt file, Visual Basic always starts a new line when it reaches a comma. I'm writing a program to add HTML to a txt-file, but because there are a lot of commas in those txt-files, my HTML-page looks crappy (with very short sentences).

Is there anything I can do to make Visual Basic input a comma, if there is a comma in the file?

Stop After Second Comma
i am trying to process a txt file line by line.
I am trying to get it to add items to a list but only when it gets to the second comman on the line.

eg of line....
93849, some_name, bla blabl, 98989, 938493

I only need to extract the line before the second comma....
eg: extract only this....
93849, some_name

I have tried if statments and such but i cant get it
Any ideas?

Thanks for any help,

The Point As Comma, Can I?
hi there, my first journey here begin with the following post.

How can i allow the dbgrid accept the point as a comma?

when i put the point "." in the input of a column/numeric field
i wanna it have the same meaning of the ",".
ex: 12.45, after i confirm the input it will be like 1245...
i want it like the same 12,45 i have if i write 12,45...

can someone help me in that ?


Strip Comma
What's the syntax to allow me to strip comma's out of my number in VB?

Tab/comma Delimiter
How can I set Visual Basic to recognize tab as a delimiter, just as it does with comma?

Rather new to VB programming and writing a macro in an M$Excel sheet, which should read a data file created in some x-ternal measureing equipment into the spreadsheet for analysis. The data lie in a flat, tab-separated ASCII-file looking like:

yyyymmdd [TAB] hh:mm:ss [TAB] id1 [TAB] data1 [TAB] data2 [TAB] data3 …
yyyymmdd [TAB] hh:mm:ss [TAB] id2 [TAB] data1 [TAB] data2 [TAB] data3 …

The date is purely numerical, time elements are separated with ':' as shown, id consists of both numerical and letter characters and all data are purely numerical.

The macro will not read this properly, as the tab is recognized as a delimiter between numbers only; not between strings. In a string it is only regarded as another char.
If I define the id's as a strings, they will read all the rest of the line including all datavalues. If I define them as numerical the reading of time and id is skipped because they contain char's.

If I manually replace the tab's with commas, the macro reads all data nicely and even recognizes the 'hh:mm:ss' as a time variable.

However, this external equipment stores only with tabs as delimiter, no quotes around characters, no dashes between date-elements and it can't be altered!!!

Or is there a way to wring the command "Dim Var1 as String" so that it reads only until the next tab?

Thanx in advance

Copyright © 2005-08, All rights reserved