Parsing text containing a tilde "~"?
I have text in column A which I want to manipulate. The format of the text on the left, text on the right, seperated by " ~ " (space-tilde-space). The text on either sides of the space-tilde-space may contain spaces, slashes, backslashes, pound signs, ampersands, etc. What I'd really like to do is swap the positions of the the text on the other side of the tilde and replace the tilde with a hyphen. In other words, all the left-side text up to the space-tilde-space delimiter moves to the right of the space-tilde-space delimiter and the right-side text moves to the left of the space-tilde-space. For example, Butterfield 8 ~ Elizabeth Taylor becomes Elizabeth Taylor - Butterfield 8 Any help is appreciated.
|
0 |
|
Utf
1/30/2010 4:08:01 AM
excel.misc
78881 articles. 
5 followers.
6 Replies
1146 Views
give this a try, if the data is in A1
=RIGHT(A1,LEN(A1)-FIND("~",A1,1)) & "-" & LEFT(A1,FIND("~",A1,1)-1)
--
Gary Keramidas
Excel 2003
"Big UT Fan" <BigUTFan@discussions.microsoft.com> wrote in message
news:EEAB9BDB-AC16-4B8E-AE3B-5485912E465C@microsoft.com...
>I have text in column A which I want to manipulate. The format of the text
> on the left, text on the right, seperated by " ~ " (space-tilde-space). The
> text on either sides of the space-tilde-space may contain spaces, slashes,
> backslashes, pound signs, ampersands, etc. What I'd really like to do is
> swap the positions of the the text on the other side of the tilde and replace
> the tilde with a hyphen. In other words, all the left-side text up to the
> space-tilde-space delimiter moves to the right of the space-tilde-space
> delimiter and the right-side text moves to the left of the space-tilde-space.
> For example,
>
> Butterfield 8 ~ Elizabeth Taylor
>
> becomes
>
> Elizabeth Taylor - Butterfield 8
>
> Any help is appreciated.
|
|
0 |
|
Gary
1/30/2010 4:20:44 AM
ok...I figured out how to replace ~...you have to use ~~ then the replacement text. I'd still like to swap the text left & right of the space-hyphen-space delimiter. Thanks. "Big UT Fan" wrote: > I have text in column A which I want to manipulate. The format of the text > on the left, text on the right, seperated by " ~ " (space-tilde-space). The > text on either sides of the space-tilde-space may contain spaces, slashes, > backslashes, pound signs, ampersands, etc. What I'd really like to do is > swap the positions of the the text on the other side of the tilde and replace > the tilde with a hyphen. In other words, all the left-side text up to the > space-tilde-space delimiter moves to the right of the space-tilde-space > delimiter and the right-side text moves to the left of the space-tilde-space. > For example, > > Butterfield 8 ~ Elizabeth Taylor > > becomes > > Elizabeth Taylor - Butterfield 8 > > Any help is appreciated.
|
|
0 |
|
Utf
1/30/2010 4:33:01 AM
this would even out the spaces and hyphens
=RIGHT(A1,LEN(A1)-FIND("~",A1,1)-1) & "- " & LEFT(A1,FIND("~",A1,1)-1)
--
Gary Keramidas
Excel 2003
"Big UT Fan" <BigUTFan@discussions.microsoft.com> wrote in message
news:F488F850-2714-488D-B6B8-F079169D5CE5@microsoft.com...
> ok...I figured out how to replace ~...you have to use ~~ then the replacement
> text. I'd still like to swap the text left & right of the space-hyphen-space
> delimiter. Thanks.
>
> "Big UT Fan" wrote:
>
>> I have text in column A which I want to manipulate. The format of the text
>> on the left, text on the right, seperated by " ~ " (space-tilde-space). The
>> text on either sides of the space-tilde-space may contain spaces, slashes,
>> backslashes, pound signs, ampersands, etc. What I'd really like to do is
>> swap the positions of the the text on the other side of the tilde and replace
>> the tilde with a hyphen. In other words, all the left-side text up to the
>> space-tilde-space delimiter moves to the right of the space-tilde-space
>> delimiter and the right-side text moves to the left of the space-tilde-space.
>> For example,
>>
>> Butterfield 8 ~ Elizabeth Taylor
>>
>> becomes
>>
>> Elizabeth Taylor - Butterfield 8
>>
>> Any help is appreciated.
|
|
0 |
|
Gary
1/30/2010 4:36:46 AM
Try this
=MID(F10,FIND("~",F10)+1,99)&" - "&LEFT(F10,FIND(" ~",F10))
HTH
Regards,
Howard
"Big UT Fan" <BigUTFan@discussions.microsoft.com> wrote in message
news:EEAB9BDB-AC16-4B8E-AE3B-5485912E465C@microsoft.com...
>I have text in column A which I want to manipulate. The format of the text
> on the left, text on the right, seperated by " ~ " (space-tilde-space).
> The
> text on either sides of the space-tilde-space may contain spaces, slashes,
> backslashes, pound signs, ampersands, etc. What I'd really like to do is
> swap the positions of the the text on the other side of the tilde and
> replace
> the tilde with a hyphen. In other words, all the left-side text up to the
> space-tilde-space delimiter moves to the right of the space-tilde-space
> delimiter and the right-side text moves to the left of the
> space-tilde-space.
> For example,
>
> Butterfield 8 ~ Elizabeth Taylor
>
> becomes
>
> Elizabeth Taylor - Butterfield 8
>
> Any help is appreciated.
|
|
0 |
|
L
1/30/2010 4:40:34 AM
Not working for me...
"L. Howard Kittle" wrote:
> Try this
>
> =MID(F10,FIND("~",F10)+1,99)&" - "&LEFT(F10,FIND(" ~",F10))
>
> HTH
> Regards,
> Howard
>
> "Big UT Fan" <BigUTFan@discussions.microsoft.com> wrote in message
> news:EEAB9BDB-AC16-4B8E-AE3B-5485912E465C@microsoft.com...
> >I have text in column A which I want to manipulate. The format of the text
> > on the left, text on the right, seperated by " ~ " (space-tilde-space).
> > The
> > text on either sides of the space-tilde-space may contain spaces, slashes,
> > backslashes, pound signs, ampersands, etc. What I'd really like to do is
> > swap the positions of the the text on the other side of the tilde and
> > replace
> > the tilde with a hyphen. In other words, all the left-side text up to the
> > space-tilde-space delimiter moves to the right of the space-tilde-space
> > delimiter and the right-side text moves to the left of the
> > space-tilde-space.
> > For example,
> >
> > Butterfield 8 ~ Elizabeth Taylor
> >
> > becomes
> >
> > Elizabeth Taylor - Butterfield 8
> >
> > Any help is appreciated.
>
>
> .
>
|
|
0 |
|
Utf
1/30/2010 5:47:01 AM
This DOES work...forgot I replaced the "~" with "-". Thanks!
"L. Howard Kittle" wrote:
> Try this
>
> =MID(F10,FIND("~",F10)+1,99)&" - "&LEFT(F10,FIND(" ~",F10))
>
> HTH
> Regards,
> Howard
>
> "Big UT Fan" <BigUTFan@discussions.microsoft.com> wrote in message
> news:EEAB9BDB-AC16-4B8E-AE3B-5485912E465C@microsoft.com...
> >I have text in column A which I want to manipulate. The format of the text
> > on the left, text on the right, seperated by " ~ " (space-tilde-space).
> > The
> > text on either sides of the space-tilde-space may contain spaces, slashes,
> > backslashes, pound signs, ampersands, etc. What I'd really like to do is
> > swap the positions of the the text on the other side of the tilde and
> > replace
> > the tilde with a hyphen. In other words, all the left-side text up to the
> > space-tilde-space delimiter moves to the right of the space-tilde-space
> > delimiter and the right-side text moves to the left of the
> > space-tilde-space.
> > For example,
> >
> > Butterfield 8 ~ Elizabeth Taylor
> >
> > becomes
> >
> > Elizabeth Taylor - Butterfield 8
> >
> > Any help is appreciated.
>
>
> .
>
|
|
0 |
|
Utf
1/30/2010 5:49:01 AM
Similar Artilces:
Replacing ~ with text
i have a spreadsheet that gets delivered and some of the cells simply contain a "~". i want to replace the "~' with "N/A". however, when I use the Edit:Replace command, get a message back that says: "Microsoft Office Excel cannot find any data to replace. Check if your searc formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet." This worksheet is definitely NOT protected. Help! Hi ~ is a special character to Exc...
Parsing text containing a tilde "~"?
I have text in column A which I want to manipulate. The format of the text on the left, text on the right, seperated by " ~ " (space-tilde-space). The text on either sides of the space-tilde-space may contain spaces, slashes, backslashes, pound signs, ampersands, etc. What I'd really like to do is swap the positions of the the text on the other side of the tilde and replace the tilde with a hyphen. In other words, all the left-side text up to the space-tilde-space delimiter moves to the right of the space-tilde-space delimiter and the right-side text moves to th...
Define a development approach to Parse/Load/Search an XML document of size ~1GB
Hi All i have given a problem set in which i need to parse/load/search a xml document of near about 1 GB in dot net. Please help me in this also do not have the luxury to dump the data into a relational database Sandeep *** Sent via Developersdex http://www.developersdex.com *** Sandeep Akhare wrote: > i have given a problem set in which i need to parse/load/search a xml > document of near about 1 GB in dot net. XmlReader (respectively XmlTextReader in .NET 1.x) parses XML of any size in a forwards only pull parsing mode node by node so memory consumption by the reade...
How can I send text to the window with the keyboard focus~?
I want to send "strInChar" to the window with the keyboard focus,the code below doesnot work,Is there anyone can help me? strInChar=CString(buf).Left(dwLength); HWND GetFocus(); // ExtTextOut(0,0,ETO_CLIPPED|ETO_OPAQUE,NULL,strInChar,-1,NULL); // The code you have shown does not make much sense. You cannot *send* text to a window. The window needs to paint whatever text is there in its OnPaint(WM_PAINT). I have no idea if you own the window or its out of process. You can do this only if you own the window or an out of process window responds to some external messages . Th...
Using VLOOKUP where data may contain "~" characters
Hello, I'm trying to use VLOOKUP to determine which row in a table corresponds to my data value (or return #N/A if it's not there). All goes fine, except when the data contains a "~" - which appears to be a special escape character as far as Excel is concerned (see example below). As a temporary measure I've replaced "~" with "$" in my data, but I'd like to know if there is a better solution. Result = VLOOKUP(A2,$B$2:$C$6,2,FALSE) Data Lookup Table Result a@a a!a 1 3 a~a 2 a@a 3 a#a 4 a$a 5 -------------------...
Outlook Express File Named Only With A Tilde ~
Sometimes when I close Outlook Express, I get a file on my desktop that has only a tilde (~) as its name. Anybody know what's causing this? "Art Cabot" <artcabot@cabotcomputer.com> wrote in message news:178a001c44961$6010b6e0$a301280a@phx.gbl... > Sometimes when I close Outlook Express, I get a file on my > desktop that has only a tilde (~) as its name. > > Anybody know what's causing this? Yes please, I'd love to know this too! -- Jason Aspinall email address supplied on request "Jason Aspinall" <fakeemail@nowwhere.con> wrote in...
i have a spreadsheet that gets delivered and some of the cells simply contain a "~". i want to replace the "~' with "N/A". however, when I use the Edit:Replace command, get a message back that says: "Microsoft Office Excel cannot find any data to replace. Check if your searc formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet." This worksheet is definitely NOT protected. Help! Hi ~ is a special character to Exc...
Parsing text containing a tilde "~"?
I have text in column A which I want to manipulate. The format of the text on the left, text on the right, seperated by " ~ " (space-tilde-space). The text on either sides of the space-tilde-space may contain spaces, slashes, backslashes, pound signs, ampersands, etc. What I'd really like to do is swap the positions of the the text on the other side of the tilde and replace the tilde with a hyphen. In other words, all the left-side text up to the space-tilde-space delimiter moves to the right of the space-tilde-space delimiter and the right-side text moves to th...
Define a development approach to Parse/Load/Search an XML document of size ~1GB
Hi All i have given a problem set in which i need to parse/load/search a xml document of near about 1 GB in dot net. Please help me in this also do not have the luxury to dump the data into a relational database Sandeep *** Sent via Developersdex http://www.developersdex.com *** Sandeep Akhare wrote: > i have given a problem set in which i need to parse/load/search a xml > document of near about 1 GB in dot net. XmlReader (respectively XmlTextReader in .NET 1.x) parses XML of any size in a forwards only pull parsing mode node by node so memory consumption by the reade...
How can I send text to the window with the keyboard focus~?
I want to send "strInChar" to the window with the keyboard focus,the code below doesnot work,Is there anyone can help me? strInChar=CString(buf).Left(dwLength); HWND GetFocus(); // ExtTextOut(0,0,ETO_CLIPPED|ETO_OPAQUE,NULL,strInChar,-1,NULL); // The code you have shown does not make much sense. You cannot *send* text to a window. The window needs to paint whatever text is there in its OnPaint(WM_PAINT). I have no idea if you own the window or its out of process. You can do this only if you own the window or an out of process window responds to some external messages . Th...
Using VLOOKUP where data may contain "~" characters
Hello, I'm trying to use VLOOKUP to determine which row in a table corresponds to my data value (or return #N/A if it's not there). All goes fine, except when the data contains a "~" - which appears to be a special escape character as far as Excel is concerned (see example below). As a temporary measure I've replaced "~" with "$" in my data, but I'd like to know if there is a better solution. Result = VLOOKUP(A2,$B$2:$C$6,2,FALSE) Data Lookup Table Result a@a a!a 1 3 a~a 2 a@a 3 a#a 4 a$a 5 -------------------...
Outlook Express File Named Only With A Tilde ~
Sometimes when I close Outlook Express, I get a file on my desktop that has only a tilde (~) as its name. Anybody know what's causing this? "Art Cabot" <artcabot@cabotcomputer.com> wrote in message news:178a001c44961$6010b6e0$a301280a@phx.gbl... > Sometimes when I close Outlook Express, I get a file on my > desktop that has only a tilde (~) as its name. > > Anybody know what's causing this? Yes please, I'd love to know this too! -- Jason Aspinall email address supplied on request "Jason Aspinall" <fakeemail@nowwhere.con> wrote in...