Helping Parsing Excel Data
 

I am trying to parse out words from a cell of data that are all between
the same characters ( ' )

For example, I have in one cell:

There is an error with 'Tom','Paul','Nicole' but then not with (or
including) 'Bill', 'Nancy'....

I am sorry I can't put the real data here but this is same scenario.

What I would like is in one cell to get Tom,Paul,Nicole,Bill,Nancy

instead of all the garbage in between.

Any ideas with this?

Please help if you can.

Thanks.

Conor Finnegan

Helping Parsing Excel Data
 

Are you trying to get this info into another cell?

If yes, you could use a UDF like this:

Option Explicit
Function GetNames(rng As Range) As String

Dim myOutStr As String
Dim myInStr As String
Dim myChar As String
Dim InName As Boolean
Dim iCtr As Long

myInStr = rng.Cells(1).Value
myOutStr = ""
If InStr(1, myInStr, "'", vbTextCompare) = 0 Then
'do nothing
Else
InName = False
For iCtr = 1 To Len(myInStr)
myChar = Mid(myInStr, iCtr, 1)
If myChar = "'" Then
InName = Not InName
If InName = False Then
If myOutStr = "" Then
'do nothing
Else
'add a comma
myOutStr = myOutStr & ","
End If
End If
ElseIf InName = True Then
myOutStr = myOutStr & myChar
End If
Next iCtr
End If

If Right(myOutStr, 1) = "," Then
myOutStr = Left(myOutStr, Len(myOutStr) - 1)
End If

GetNames = myOutStr

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=GetNames(A1)

Where A1 holds that string.



wrote:

I am trying to parse out words from a cell of data that are all between
the same characters ( ' )

For example, I have in one cell:

There is an error with 'Tom','Paul','Nicole' but then not with (or
including) 'Bill', 'Nancy'....

I am sorry I can't put the real data here but this is same scenario.

What I would like is in one cell to get Tom,Paul,Nicole,Bill,Nancy

instead of all the garbage in between.

Any ideas with this?

Please help if you can.

Thanks.

Conor Finnegan

--

Dave Peterson

Helping Parsing Excel Data
 

Thanks to both of you. It worked great...both of them did.

Thanks again.

Conor

Dave Peterson wrote:
Are you trying to get this info into another cell?

If yes, you could use a UDF like this:

Option Explicit
Function GetNames(rng As Range) As String

Dim myOutStr As String
Dim myInStr As String
Dim myChar As String
Dim InName As Boolean
Dim iCtr As Long

myInStr = rng.Cells(1).Value
myOutStr = ""
If InStr(1, myInStr, "'", vbTextCompare) = 0 Then
'do nothing
Else
InName = False
For iCtr = 1 To Len(myInStr)
myChar = Mid(myInStr, iCtr, 1)
If myChar = "'" Then
InName = Not InName
If InName = False Then
If myOutStr = "" Then
'do nothing
Else
'add a comma
myOutStr = myOutStr & ","
End If
End If
ElseIf InName = True Then
myOutStr = myOutStr & myChar
End If
Next iCtr
End If

If Right(myOutStr, 1) = "," Then
myOutStr = Left(myOutStr, Len(myOutStr) - 1)
End If

GetNames = myOutStr

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=GetNames(A1)

Where A1 holds that string.



wrote:

I am trying to parse out words from a cell of data that are all between
the same characters ( ' )

For example, I have in one cell:

There is an error with 'Tom','Paul','Nicole' but then not with (or
including) 'Bill', 'Nancy'....

I am sorry I can't put the real data here but this is same scenario.

What I would like is in one cell to get Tom,Paul,Nicole,Bill,Nancy

instead of all the garbage in between.

Any ideas with this?

Please help if you can.

Thanks.

Conor Finnegan

--

Dave Peterson