Re: Extracting from string
- From: eliano <falinieliano@xxxxxxxxxxx>
- Date: Tue, 18 Mar 2008 20:44:27 -0700 (PDT)
Hi Phil.
Awaiting Mr. Gary, you can try:
Sub SeparaAnagrafe()
Dim Riga, Uriga, I
Dim Nome, Pos, XA1, XA2
Riga = 1
Uriga = Cells(Riga, 1).End(xlDown).Row
For I = Riga To Uriga
Nome = Cells(I, 1)
Pos = InStrRev(Nome, " ")
XA1 = Left(Nome, Pos - 1)
XA2 = Mid(Nome, Pos + 1)
Cells(I, 2) = XA1
Cells(I, 3) = XA2
Cells(I, 4) = XA2 & " " & XA1
Next
End Sub
You can test the macro using: Di Maggio Joe in A1 and De Curtis
Antonio in A2
and vary the sub in accordance with what you want.
Saluti
Eliano
On 18 Mar, 21:26, Phil Trumpy <PhilTru...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Gary''s Student - the trim function took care of it wonderfully. Now I just
have to figure out how to handle last names with a space in them. I think I
should be able to figure that out. Thank you again.
"Phil Trumpy" wrote:
Thanks again. I should have thought of that. I'll add that at the beginning
of the macro and give it another shot.
"Gary''s Student" wrote:
I was able to duplicate your problem with the "Alou" record.
There is an extra space after NYM.
You can use the TRIM() function in the worksheet to remove these little pests.
--
Gary''s Student - gsnu200774
"Phil Trumpy" wrote:
Thank you very much. This works almost perfectly. However, there seem to be
a few records that did not get extracted correctly. Also, I did not account
for a LastName that could have a space in it (De Aza in this case). Here is
an example of one of the records that didn't seem to work:
Before
Col A
Alou, Moises RF NYM
After
Col A Col B Col C Col D
Alou Moises RF NYM
Any ideas?
"Gary''s Student" wrote:
This macro assumes that the fields are separated by a single space.. The
macro does the equivalent to Text-to-Columns. It splits the data up into
either 5 or 4 parts.
If there are only four parts, then the middle initial is missing and the
data is placed into positions 1,2,4,5.
If there are five parts, then the middle iniial is present and the data is
placed into positions 1,2,3,4,5:
Sub SplitUm()
Sheets("Sheet1").Activate
Set w2 = Sheets("Sheet2")
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
s = Split(Cells(i, 1).Value)
u = UBound(s) + 1
w2.Cells(i, 1).Value = Replace(s(0), ",", "")
w2.Cells(i, 2).Value = s(1)
w2.Cells(i, 5) = s(u - 1)
w2.Cells(i, 4) = s(u - 2)
If u = 5 Then
w2.Cells(i, 3) = s(2)
End If
Next
End Sub
--
Gary''s Student - gsnu200774
"Phil Trumpy" wrote:
I'm using Excel 2007 - Data is in column A
I have a data set that I am trying to import. The first column really
should be 5 separate columns, but has been created as one string.. I need to
separate into 5 columns. the data looks like this:
LastName, FirstName MI. Position Team
LastName and FirstName can be ulimited characters
MI can be 1 or 2 characters followed by a period
Position is 1 or 2 characters
Team is 3 characters
I attempted to use Text to Columns, but the problem is that the middile
initial does not always exist. If I can get the middle initial to a separate
column, I can use text to columns for the rest. I would prefer to just run
one macro to take care of the whole thing since I will have a different file
each year that has the same data structure. Sorry if I didn't provide enough
info. Thanks in advance.- Nascondi testo tra virgolette -
- Mostra testo tra virgolette -
.
- References:
- RE: Extracting from string
- From: Gary''s Student
- RE: Extracting from string
- From: Phil Trumpy
- RE: Extracting from string
- From: Gary''s Student
- RE: Extracting from string
- From: Phil Trumpy
- RE: Extracting from string
- From: Phil Trumpy
- RE: Extracting from string
- Prev by Date: Re: Work-around for blocking of invalid certificates Excel 2007
- Next by Date: Re: returning back to loop check condition without completing the loop
- Previous by thread: RE: Extracting from string
- Next by thread: Re: Extracting from string
- Index(es):
Relevant Pages
|
