MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

New to VBA and trying to separate data

  Asked By: Adelisa    Date: Feb 14    Category: MS Office    Views: 2098

I am a VBA novice, and could use a little help with an assignment.
I am trying to write code that will separate a lot of information
contained in one column. Basically, each cell contains a name, #,
and state, separated by a hyphen or parenthesis. I am separating
this information into 3 separate columns. My problem is what
command to use to recognize the separator.



6 Answers Found

Answer #1    Answered By: Nahal Malik     Answered On: Feb 14

Can you give some example of the data  within the column.

You can find a separator with excel function find.special and use this
with vba  code and coninue searching from that point on to the next
separator until it run into the length of the string

Answer #2    Answered By: Billy Evans     Answered On: Feb 14

The data  looks a little something like this

Firstname,Lastname- Chicago,IL- (5557774545)

I'm trying to get the names, city, state, and phone # separated. As
far as the find.special function, I do not see it anywhere. Any

Answer #3    Answered By: Isam Bashara     Answered On: Feb 14

try this Statement bellow,
put original data  in column  A, basic form : Firstname,Lastname- Chicago,IL-

the result :
Column C = Firstname,Lastname, B = Chicago, Column D = IL, column E =

It's easy !

Sub Separate()

Dim var1, var2, Var3, var4, Var5, Var6

var2 = 1


var1 = Range("A" & var2)

If var1 = "" Then Exit Do

Var3 = InStr(var1, "-")

Range("B" & var2) = Left(var1, Var3 - 1)

var4 = InStrRev(var1, ",")

Range("C" & var2) = Trim(Mid(var1, Var3 + 1, var4 - Var3 - 1))

Var5 = InStrRev(var1, "-")

Range("D" & var2) = Mid(var1, var4 + 1, Var5 - var4 - 1)

Var6 = InStrRev(var1, "(")

Range("E" & var2) = Mid(var1, Var6 + 1, Len(var1) - Var6 - 1)

var2 = var2 + 1

Loop Until var1 = ""

ActiveSheet.Columns(5).NumberFormat = "General"

End Sub

Answer #4    Answered By: Volker Fischer     Answered On: Feb 14

I have had to separate  City and State before, and this is the code  that
I have used. Hopefully it will help  you get going:

Sub IdentifyState()
Dim strCell As String
Dim strState As String
Do While ActiveCell.Value <> ""
strCell = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
strState = Right(strCell, 2)
ActiveCell.FormulaR1C1 = strState
ActiveCell.Offset(1, -1).Range("a1").Select

End Sub

Answer #5    Answered By: Jack Williams     Answered On: Feb 14

In this example you used a fixed mechanisme to split the info. In this
case it's important to find where the separator is located. In a
previous answer i mentioned a function which can be used when you
perform this in a worksheet. In a macro this is converted to a search
I think it should be something like

move value to string
start looking for separator
found .. move value from start=1 to pos found to tempvalue
start looking for separator
start=posfound + 1
found .. move value from start to pos found to tempvalue-2
until end of string
write out values to columns  and move on to the next row

If I have any time left I'll see if I can give provide a working sample

Answer #6    Answered By: Victoria Hughes     Answered On: Feb 14

You may want to look at the Instr function

InStr([start, ]string1, string2[, compare])

Didn't find what you were looking for? Find more on New to VBA and trying to separate data Or get search suggestion and latest updates.