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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |