[mso] Re: separating parts of a text string in Excel
- From: "Dan Blanchard" <dblanchard@xxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Mon, 30 Dec 2002 20:04:46 -0500
Yes, pipe symbol and thanks!
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of Greg Chapman
Sent: Monday, December 30, 2002 7:19 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: separating parts of a text string in Excel
Is that a Pipe symbol in there between lastname and the email address?
My opinion is that you need to set up a macro that uses the Split
function at least once. The first part would split the cell's results at
the space. The second use would split the string at the pipe symbol. If
you're not familiar with macros and editing them, most of this message
won't make sense. Therefore, be sure to write back and say so!<g>
Assuming your spreadsheet has all these values arranged in column A and
that you have the entire 65535 rows limit used up, this macro would work
to put the first name in Column B, The last name in Column C and the
email address in Column D:
Sub BreakEmUp()
Dim i As Long
Dim strSplitMajor As Variant
Dim strSplitMinor As Variant
For i = 1 To 65535
If Not Range("A" & i).Value = "" Then
strSplitMajor = Split(Range("A" & i).Value, " ")
strSplitMinor = Split(strSplitMajor(1), "|")
Range("B" & i).Value = Trim(strSplitMajor(0))
Range("C" & i).Value = Trim(strSplitMinor(0))
Range("D" & i).Value = Trim(strSplitMinor(1))
End If
Next i
End Sub
That may or may not be the most glamorous solution but it is pretty
solid.
Greg Chapman
http://www.mousetrax.com
"Counting in binary is as easy as 01, 10, 11!
With thinking this clear, is coding really a good idea?"
> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx
> [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf Of Dan Blanchard
> Sent: Monday, December 30, 2002 5:44 PM
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] separating parts of a text string in Excel
>
>
> I've imported a mialing list into Excel in the form "firstname
> lastname|email". I need to get the 3 parts into separate
> cells so I can
> re-combine them into another format (without manually cutting
> and pasting hundreds of email addresses). Is there an
> "un-concatenate" function or something similar? Dan
>
>
>
> *************************************************************
> You are receiving this mail because you subscribed to
> mso@xxxxxxxxxxxxx or MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, send an email to
> mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
>
> Or, visit the group's homepage and use the dropdown menu.
> This will also allow you to change your email settings to
> digest or vacation (no mail). //www.freelists.org/webpage/mso
>
> To be able to use the files section for sharing files with
> the group, send a request to mso-moderators@xxxxxxxxxxxxx and
> you will be sent an invitation with instructions. Once you
> are a member of the files group, you can go here to
> upload/download files: http://www.smartgroups.com/vault/msofiles
> *************************************************************
>
>
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
